[Home] [Help]
PACKAGE BODY: APPS.PER_COMPETENCE_DATA_PUMP
Source
1 PACKAGE BODY PER_COMPETENCE_DATA_PUMP AS
2 /* $Header: pecompdp.pkb 115.4 2004/03/18 10:15:40 ynegoro noship $ */
3 --
4 -- Declare local variables
5 --
6 END_OF_TIME constant date := to_date('4712/12/31', 'YYYY/MM/DD');
7 START_OF_TIME constant date := to_date('0001/01/01', 'YYYY/MM/DD');
8 HR_API_G_VARCHAR2 constant varchar2(128) := hr_api.g_varchar2;
9 HR_API_G_NUMBER constant number := hr_api.g_number;
10 HR_API_G_DATE constant date := hr_api.g_date;
11 l_package_name VARCHAR2(30) DEFAULT 'PER_COMPETENCE_DATA_PUMP.';
12 -- -------------------------------------------------------------------------
13 -- --------------------< get_rsc_old_id >------------------------------
14 -- -------------------------------------------------------------------------
15 FUNCTION get_rsc_old_id
16 (p_data_pump_always_call IN varchar2
17 ,p_old_rating_scale_name IN VARCHAR2
18 ,p_business_group_id IN NUMBER)
19 RETURN BINARY_INTEGER
20 IS
21 l_rating_scale_id NUMBER DEFAULT null;
22 BEGIN
23
24 IF p_old_rating_scale_name is NULL then
25
26 return null;
27
28 ELSIF p_old_rating_scale_name = hr_api_g_varchar2 then
29
30 return hr_api_g_number;
31
32 ELSE
33
34 IF p_business_group_id is null THEN
35
36 SELECT rating_scale_id
37 INTO l_rating_scale_id
38 FROM per_rating_scales
39 WHERE name = p_old_rating_scale_name
40 AND business_group_id is null;
41
42 ELSE
43
44 SELECT rating_scale_id
45 INTO l_rating_scale_id
46 FROM per_rating_scales
47 WHERE name = p_old_rating_scale_name
48 AND business_group_id = p_business_group_id;
49
50 END IF;
51
52 END IF;
53
54 RETURN(l_rating_scale_id);
55 EXCEPTION
56 WHEN OTHERS THEN
57 hr_data_pump.fail('get_rsc_old_id'
58 , sqlerrm
59 , p_old_rating_scale_name
60 , p_business_group_id);
61 RAISE;
62 END get_rsc_old_id;
63 -- -------------------------------------------------------------------------
64 -- --------------------< get_rating_scale_id >------------------------------
65 -- -------------------------------------------------------------------------
66 FUNCTION get_rating_scale_id
67 (p_data_pump_always_call IN varchar2
68 ,p_rating_scale_name IN VARCHAR2
69 ,p_business_group_id IN NUMBER)
70 RETURN BINARY_INTEGER
71 IS
72 l_rating_scale_id NUMBER DEFAULT null;
73 BEGIN
74
75 IF p_rating_scale_name is NULL then
76
77 return null;
78
79 ELSIF p_rating_scale_name = hr_api_g_varchar2 then
80
81 return hr_api_g_number;
82
83 ELSE
84
85 IF p_business_group_id is null THEN
86
87 SELECT rating_scale_id
88 INTO l_rating_scale_id
89 FROM per_rating_scales
90 WHERE name = p_rating_scale_name
91 AND business_group_id is null;
92
93 ELSE
94
95 SELECT rating_scale_id
96 INTO l_rating_scale_id
97 FROM per_rating_scales
98 WHERE name = p_rating_scale_name
99 AND business_group_id = p_business_group_id;
100
101 END IF;
102
103 END IF;
104
105 RETURN(l_rating_scale_id);
106 EXCEPTION
107 WHEN OTHERS THEN
108 hr_data_pump.fail('get_rating_scale_id'
109 , sqlerrm
110 , p_rating_scale_name
111 , p_business_group_id);
112 RAISE;
113 END get_rating_scale_id;
114 -- -------------------------------------------------------------------------
115 -- --------------------< get_rsc_ovn >---------------------------
116 -- -------------------------------------------------------------------------
117 -- DESCRIPTION
118 -- This function returns the ovn of a rating scale
119 --
120 FUNCTION get_rsc_ovn
121 (p_data_pump_always_call IN varchar2
122 ,p_old_rating_scale_name IN VARCHAR2
123 ,p_business_group_id IN NUMBER)
124 RETURN BINARY_INTEGER
125 IS
126
127 l_rsc_ovn per_rating_scales.object_version_number%TYPE;
128
129 BEGIN
130
131 IF p_old_rating_scale_name is NULL then
132
133 return null;
134
135 ELSIF p_old_rating_scale_name = hr_api_g_varchar2 then
136
137 return hr_api_g_number;
138
139 ELSE
140
141 IF p_business_group_id is null THEN
142
143 SELECT object_version_number
144 INTO l_rsc_ovn
145 FROM per_rating_scales
146 WHERE name = p_old_rating_scale_name
147 AND business_group_id is null;
148
149 ELSE
150
151 SELECT object_version_number
152 INTO l_rsc_ovn
153 FROM per_rating_scales
154 WHERE name = p_old_rating_scale_name
155 AND business_group_id = p_business_group_id;
156
157 END IF;
158
159 END IF;
160
161 return l_rsc_ovn;
162
163 EXCEPTION
164 WHEN OTHERS THEN
165 hr_data_pump.fail('get_rsc_ovn'
166 , sqlerrm
167 , p_old_rating_scale_name
168 , p_business_group_id);
169 RAISE;
170 END get_rsc_ovn;
171 -- -------------------------------------------------------------------------
172 -- ------------< get_parent_comp_element_id >-------------------------
173 -- -------------------------------------------------------------------------
174 -- DESCRIPTION
175 -- This function returns the
176 --
177 FUNCTION get_parent_comp_element_id
178 RETURN BINARY_INTEGER
179 IS
180 BEGIN
181 return (null);
182 EXCEPTION
183 WHEN OTHERS THEN
184 hr_data_pump.fail('get_parent_comp_element_id'
185 , sqlerrm
186 );
187 RAISE;
188 END get_parent_comp_element_id;
189 -- -------------------------------------------------------------------------
190 -- --------------------< get_competence_id >--------------------------------
191 -- -------------------------------------------------------------------------
192 FUNCTION get_competence_id
193 (p_data_pump_always_call IN varchar2
194 ,p_competence_name IN VARCHAR2
195 ,p_business_group_id IN NUMBER)
196 RETURN BINARY_INTEGER
197 IS
198 l_competence_id NUMBER DEFAULT null;
199 BEGIN
200
201 IF p_competence_name is NULL then
202
203 return null;
204
205 ELSIF p_competence_name = hr_api_g_varchar2 then
206
207 return hr_api_g_number;
208
209 ELSE
210
211 IF p_business_group_id is null THEN
212
213 SELECT competence_id
214 INTO l_competence_id
215 FROM per_competences_vl
216 WHERE name = p_competence_name
217 AND business_group_id is null;
218
219 ELSE
220
221 SELECT competence_id
222 INTO l_competence_id
223 FROM per_competences_vl
224 WHERE name = p_competence_name
225 AND business_group_id = p_business_group_id;
226
227 END IF;
228
229 END IF;
230
231 RETURN(l_competence_id);
232 EXCEPTION
233 WHEN OTHERS THEN
234 hr_data_pump.fail('get_competence_id'
235 , sqlerrm
236 , p_competence_name
237 , p_business_group_id);
238 RAISE;
239 END get_competence_id;
240 -- -------------------------------------------------------------------------
241 -- --------------------< get_cpn_ovn >---------------------------
242 -- -------------------------------------------------------------------------
243 -- DESCRIPTION
244 -- This function returns the ovn of a competence
245 --
246 FUNCTION get_cpn_ovn
247 (p_data_pump_always_call IN varchar2
248 ,p_competence_name IN VARCHAR2
249 ,p_business_group_id IN NUMBER)
250 RETURN BINARY_INTEGER
251 IS
252 l_cpn_ovn NUMBER DEFAULT null;
253 BEGIN
254
255 IF p_competence_name is NULL then
256
257 return null;
258
259 ELSIF p_competence_name = hr_api_g_varchar2 then
260
261 return hr_api_g_number;
262
263 ELSE
264
265 IF p_business_group_id is null THEN
266
267 SELECT object_version_number
268 INTO l_cpn_ovn
269 FROM per_competences_vl
270 WHERE name = p_competence_name
271 AND business_group_id is null;
272
273 ELSE
274
275 SELECT object_version_number
276 INTO l_cpn_ovn
277 FROM per_competences_vl
278 WHERE name = p_competence_name
279 AND business_group_id = p_business_group_id;
280
281 END IF;
282
283 END IF;
284
285 RETURN(l_cpn_ovn);
286 EXCEPTION
287 WHEN OTHERS THEN
288 hr_data_pump.fail('get_cpn_ovn'
289 , sqlerrm
290 , p_competence_name
291 , p_business_group_id);
292 RAISE;
293 END get_cpn_ovn;
294 -- -------------------------------------------------------------------------
295 -- --------------------< get_enterprise_id >--------------------------------
296 -- -------------------------------------------------------------------------
297 FUNCTION get_enterprise_id
298 RETURN BINARY_INTEGER
299 IS
300 BEGIN
301 RETURN (null);
302 EXCEPTION
303 WHEN OTHERS THEN
304 hr_data_pump.fail('get_enteprise_id'
305 , sqlerrm
306 );
307 RAISE;
308 END get_enterprise_id;
309 -- -------------------------------------------------------------------------
310 -- --------------------< get_proficiency_level_id >-------------------------
311 -- -------------------------------------------------------------------------
312 -- DESCRIPTION
313 -- This function returns the
314 --
315 FUNCTION get_proficiency_level_id
316 RETURN BINARY_INTEGER
317 IS
318 BEGIN
319 RETURN (null);
320 EXCEPTION
321 WHEN OTHERS THEN
322 hr_data_pump.fail('get_proficiency_level_id'
323 , sqlerrm
324 );
325 RAISE;
326 END get_proficiency_level_id;
327 -- -------------------------------------------------------------------------
328 -- --------------------< get_position_id >----------------------------------
329 -- -------------------------------------------------------------------------
330 -- DESCRIPTION
331 -- This function returns the
332 --
333 FUNCTION get_position_id
334 RETURN BINARY_INTEGER
335 IS
336 BEGIN
337 RETURN (null);
338 EXCEPTION
339 WHEN OTHERS THEN
340 hr_data_pump.fail('get_position_id'
341 , sqlerrm
342 );
343 RAISE;
344 END get_position_id;
345 -- -------------------------------------------------------------------------
346 -- --------------------< get_activity_version_id >--------------------------
347 -- -------------------------------------------------------------------------
348 -- DESCRIPTION
349 -- This function returns the
350 --
351 FUNCTION get_activity_version_id
352 RETURN BINARY_INTEGER
353 IS
354 BEGIN
355 RETURN (null);
356 EXCEPTION
357 WHEN OTHERS THEN
358 hr_data_pump.fail('get_activity_version_id'
359 , sqlerrm
360 );
361 RAISE;
362 END get_activity_version_id;
363 -- -------------------------------------------------------------------------
364 -- --------------------< get_person_id >------------------------------------
365 -- -------------------------------------------------------------------------
366 -- DESCRIPTION
367 -- This function returns the
368 --
369 FUNCTION get_person_id
370 RETURN BINARY_INTEGER
371 IS
372 BEGIN
373 RETURN (null);
374 EXCEPTION
375 WHEN OTHERS THEN
376 hr_data_pump.fail('get_person_id'
377 , sqlerrm
378 );
379 RAISE;
380 END get_person_id;
381 -- -------------------------------------------------------------------------
382 -- --------------------< get_high_proficiency_level_id >--------------------
383 -- -------------------------------------------------------------------------
384 -- DESCRIPTION
385 -- This function returns the
386 --
387 FUNCTION get_high_proficiency_level_id
388 RETURN BINARY_INTEGER
389 IS
390 BEGIN
391 RETURN (null);
392 EXCEPTION
393 WHEN OTHERS THEN
394 hr_data_pump.fail('get_high_proficiency_level_id'
395 , sqlerrm
396 );
397 RAISE;
398 END get_high_proficiency_level_id;
399 -- -------------------------------------------------------------------------
400 -- --------------------< get_assessment_id >--------------------------------
401 -- -------------------------------------------------------------------------
402 -- DESCRIPTION
403 -- This function returns the
404 --
405 FUNCTION get_assessment_id
406 RETURN BINARY_INTEGER
407 IS
408 BEGIN
409 RETURN (null);
410 EXCEPTION
411 WHEN OTHERS THEN
412 hr_data_pump.fail('get_assessment_id'
413 , sqlerrm
414 );
415 RAISE;
416 END get_assessment_id;
417 -- -------------------------------------------------------------------------
418 -- --------------------< get_assessment_type_id >---------------------------
419 -- -------------------------------------------------------------------------
420 -- DESCRIPTION
421 -- This function returns the
422 --
423 FUNCTION get_assessment_type_id
424 RETURN BINARY_INTEGER
425 IS
426 BEGIN
427 RETURN (null);
428 EXCEPTION
429 WHEN OTHERS THEN
430 hr_data_pump.fail('get_assessment_type_id'
431 , sqlerrm
432 );
433 RAISE;
434 END get_assessment_type_id;
435 -- -------------------------------------------------------------------------
436 -- --------------------< get_weighting_level_id >---------------------------
437 -- -------------------------------------------------------------------------
438 -- DESCRIPTION
439 -- This function returns the
440 --
441 FUNCTION get_weighting_level_id
442 RETURN BINARY_INTEGER
443 IS
444 BEGIN
445 RETURN (null);
446 EXCEPTION
447 WHEN OTHERS THEN
448 hr_data_pump.fail('get_weighting_level_id'
449 , sqlerrm
450 );
451 RAISE;
452 END get_weighting_level_id;
453
454 -- -------------------------------------------------------------------------
455 -- --------------------< get_rtl_old_id >---------------------------
456 -- -------------------------------------------------------------------------
457 -- DESCRIPTION
458 -- This function returns the rating level id
459 --
460 FUNCTION get_rtl_old_id
461 (p_data_pump_always_call IN varchar2
462 ,p_rating_scale_name IN VARCHAR2
463 ,p_competence_name IN VARCHAR2
464 ,p_old_rating_level_name IN VARCHAR2
465 ,p_business_group_id IN NUMBER)
466 RETURN BINARY_INTEGER
467 IS
468 l_rating_level_id NUMBER DEFAULT null;
469 BEGIN
470
471 IF p_old_rating_level_name is NULL then
472
473 return null;
474
475 ELSIF p_old_rating_level_name = hr_api_g_varchar2 then
476
477 return hr_api_g_number;
478
479 ELSE
480
481 IF ( p_competence_name is null AND p_rating_scale_name is not null ) THEN
482
483 SELECT rtl.rating_level_id
484 INTO l_rating_level_id
485 FROM per_rating_scales rsc , per_rating_levels rtl
486 WHERE rtl.name = p_old_rating_level_name
490 AND NVL(rsc.business_group_id,hr_api_g_number) = NVL(p_business_group_id,hr_api_g_number);
487 AND rsc.rating_scale_id = rtl.rating_scale_id
488 AND rsc.name = p_rating_scale_name
489 AND NVL(rsc.business_group_id,hr_api_g_number) = NVL(rtl.business_group_id,hr_api_g_number)
491
492 ELSIF ( p_competence_name is not null AND p_rating_scale_name is null ) THEN
493
494 SELECT rtl.rating_level_id
495 INTO l_rating_level_id
496 FROM per_competences_vl cpn , per_rating_levels rtl
497 WHERE rtl.name = p_old_rating_level_name
498 AND cpn.competence_id = rtl.competence_id
499 AND cpn.name = p_competence_name
500 AND NVL(cpn.business_group_id,hr_api_g_number) = NVL(rtl.business_group_id,hr_api_g_number)
501 AND NVL(cpn.business_group_id,hr_api_g_number) = NVL(p_business_group_id,hr_api_g_number);
502
503 END IF;
504
505 END IF;
506 RETURN(l_rating_level_id);
507 EXCEPTION
508 WHEN OTHERS THEN
509 hr_data_pump.fail('get_rtl_old_id'
510 , sqlerrm
511 , p_rating_scale_name
512 , p_competence_name
513 , p_old_rating_level_name
514 , p_business_group_id);
515 RAISE;
516 END get_rtl_old_id;
517 -- -------------------------------------------------------------------------
518 -- --------------------< get_rating_level_id >---------------------------
519 -- -------------------------------------------------------------------------
520 -- DESCRIPTION
521 -- This function returns the rating level id
522 --
523 FUNCTION get_rating_level_id
524 (p_data_pump_always_call IN varchar2
525 ,p_rating_scale_name IN VARCHAR2
526 ,p_competence_name IN VARCHAR2
527 ,p_rating_level_name IN VARCHAR2
528 ,p_business_group_id IN NUMBER)
529 RETURN BINARY_INTEGER
530 IS
531 l_rating_level_id NUMBER DEFAULT null;
532 BEGIN
533
534 IF p_rating_level_name is NULL then
535
536 return null;
537
538 ELSIF p_rating_level_name = hr_api_g_varchar2 then
539
540 return hr_api_g_number;
541
542 ELSE
543
544 IF ( p_competence_name is null AND p_rating_scale_name is not null ) THEN
545
546 SELECT rtl.rating_level_id
547 INTO l_rating_level_id
548 FROM per_rating_scales rsc , per_rating_levels rtl
549 WHERE rtl.name = p_rating_level_name
550 AND rsc.rating_scale_id = rtl.rating_scale_id
551 AND rsc.name = p_rating_scale_name
552 AND NVL(rsc.business_group_id,hr_api_g_number) = NVL(rtl.business_group_id,hr_api_g_number)
553 AND NVL(rsc.business_group_id,hr_api_g_number) = NVL(p_business_group_id,hr_api_g_number);
554
555 ELSIF ( p_competence_name is not null AND p_rating_scale_name is null ) THEN
556
557 SELECT rtl.rating_level_id
558 INTO l_rating_level_id
559 FROM per_competences_vl cpn , per_rating_levels rtl
560 WHERE rtl.name = p_rating_level_name
561 AND cpn.competence_id = rtl.competence_id
562 AND cpn.name = p_competence_name
563 AND NVL(cpn.business_group_id,hr_api_g_number) = NVL(rtl.business_group_id,hr_api_g_number)
564 AND NVL(cpn.business_group_id,hr_api_g_number) = NVL(p_business_group_id,hr_api_g_number);
565
566 END IF;
567
568 END IF;
569 RETURN(l_rating_level_id);
570 EXCEPTION
571 WHEN OTHERS THEN
572 hr_data_pump.fail('get_rating_level_id'
573 , sqlerrm
574 , p_rating_scale_name
575 , p_competence_name
576 , p_rating_level_name
577 , p_business_group_id);
578 RAISE;
579 END get_rating_level_id;
580
581 -- -------------------------------------------------------------------------
582 -- --------------------< get_rtl_ovn >---------------------------
583 -- -------------------------------------------------------------------------
584 -- DESCRIPTION
585 -- This function returns the rating level id
586 --
587 FUNCTION get_rtl_ovn
588 (p_data_pump_always_call IN varchar2
589 ,p_rating_scale_name IN VARCHAR2
590 ,p_competence_name IN VARCHAR2
591 ,p_old_rating_level_name IN VARCHAR2
592 ,p_business_group_id IN NUMBER)
593 RETURN BINARY_INTEGER
594 IS
595 l_rtl_ovn per_rating_levels.object_version_number%TYPE;
596 BEGIN
597
598 IF p_old_rating_level_name is NULL then
599
600 return null;
601
602 ELSIF p_old_rating_level_name = hr_api_g_varchar2 then
603
604 return hr_api_g_number;
605
606 ELSE
607
608 IF ( p_competence_name is null AND p_rating_scale_name is not null ) THEN
609
610 SELECT rtl.object_version_number
611 INTO l_rtl_ovn
612 FROM per_rating_scales rsc , per_rating_levels rtl
613 WHERE rtl.name = p_old_rating_level_name
614 AND rsc.rating_scale_id = rtl.rating_scale_id
615 AND rsc.name = p_rating_scale_name
616 AND NVL(rsc.business_group_id,hr_api_g_number) = NVL(rtl.business_group_id,hr_api_g_number)
617 AND NVL(rsc.business_group_id,hr_api_g_number) = NVL(p_business_group_id,hr_api_g_number);
618
619 ELSIF ( p_competence_name is not null AND p_rating_scale_name is null ) THEN
620
621 SELECT rtl.object_version_number
622 INTO l_rtl_ovn
623 FROM per_competences_vl cpn , per_rating_levels rtl
624 WHERE rtl.name = p_old_rating_level_name
625 AND cpn.competence_id = rtl.competence_id
626 AND cpn.name = p_competence_name
627 AND NVL(cpn.business_group_id,hr_api_g_number) = NVL(rtl.business_group_id,hr_api_g_number)
628 AND NVL(cpn.business_group_id,hr_api_g_number) = NVL(p_business_group_id,hr_api_g_number);
629
630 END IF;
631
632 END IF;
633 RETURN(l_rtl_ovn);
634 EXCEPTION
635 WHEN OTHERS THEN
636 hr_data_pump.fail('get_rtl_ovn'
637 , sqlerrm
638 , p_rating_scale_name
639 , p_competence_name
640 , p_old_rating_level_name
641 , p_business_group_id);
642 RAISE;
643 END get_rtl_ovn;
644 -- -------------------------------------------------------------------------
645 -- --------------------< get_valid_grade_id >---------------------------
646 -- -------------------------------------------------------------------------
647 -- DESCRIPTION
648 -- This function returns the
649 --
650 FUNCTION get_valid_grade_id
651 RETURN BINARY_INTEGER
652 IS
653 BEGIN
654 RETURN (null);
655 EXCEPTION
656 WHEN OTHERS THEN
657 hr_data_pump.fail('get_valid_grade_id'
658 , sqlerrm
659 );
660 RAISE;
661 END get_valid_grade_id;
662 -- -------------------------------------------------------------------------
663 -- --------------------< get_object_id >---------------------------
664 -- -------------------------------------------------------------------------
665 -- DESCRIPTION
666 -- This function returns the
667 --
668 FUNCTION get_object_id
669 RETURN BINARY_INTEGER
670 IS
671 BEGIN
672 RETURN (null);
673 EXCEPTION
674 WHEN OTHERS THEN
675 hr_data_pump.fail('get_object_id'
676 , sqlerrm
677 );
678 RAISE;
679 END get_object_id;
680 -- -------------------------------------------------------------------------
681 -- -----------------< get_qualification_type_id >---------------------------
682 -- -------------------------------------------------------------------------
683 FUNCTION get_qualification_type_id
684 (p_data_pump_always_call IN varchar2
685 ,p_qualification_type_name IN VARCHAR2
686 )
687 RETURN BINARY_INTEGER
688 IS
689 l_qualification_type_id NUMBER DEFAULT null;
690 BEGIN
691
692 IF p_qualification_type_name is NULL then
693
694 return null;
695
696 ELSIF p_qualification_type_name = hr_api_g_varchar2 then
697
698 return hr_api_g_number;
699
700 ELSE
701
702 SELECT qualification_type_id
703 INTO l_qualification_type_id
704 FROM per_qualification_types_vl
705 WHERE name = p_qualification_type_name;
706
707 END IF;
708
709 RETURN(l_qualification_type_id);
710 EXCEPTION
711 WHEN OTHERS THEN
712 hr_data_pump.fail('get_qualification_type_id'
713 , sqlerrm
714 , p_qualification_type_name);
715 RAISE;
716 END get_qualification_type_id;
717 --
718 --
719 END;