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: -- -------------------------------------------------------------------------
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 >------------------------------
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: -- -------------------------------------------------------------------------
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
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
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
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
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
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
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
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
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
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
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
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
485: FROM per_rating_scales rsc , per_rating_levels rtl
486: WHERE rtl.name = p_old_rating_level_name
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)
490: AND NVL(rsc.business_group_id,hr_api_g_number) = NVL(p_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:
486: WHERE rtl.name = p_old_rating_level_name
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)
490: AND NVL(rsc.business_group_id,hr_api_g_number) = NVL(p_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
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:
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;
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
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
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:
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
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:
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;
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
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
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:
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
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:
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;
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
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