[Home] [Help]
228:
229: --
230: procedure chk_mandatory
231: (p_competence_element_id
232: in per_competence_elements.competence_element_id%TYPE
233: ,p_effective_date in Date
234: ,p_mandatory
235: in per_competence_elements.mandatory%TYPE
236: ,p_object_version_number
231: (p_competence_element_id
232: in per_competence_elements.competence_element_id%TYPE
233: ,p_effective_date in Date
234: ,p_mandatory
235: in per_competence_elements.mandatory%TYPE
236: ,p_object_version_number
237: in per_competence_elements.object_version_number%TYPE
238: ) is
239: --
233: ,p_effective_date in Date
234: ,p_mandatory
235: in per_competence_elements.mandatory%TYPE
236: ,p_object_version_number
237: in per_competence_elements.object_version_number%TYPE
238: ) is
239: --
240: l_proc varchar2(72):= g_package||'chk_mandatory';
241: l_api_updating boolean;
278: -- Error: Invalid certification_method
279: hr_utility.set_location(l_proc, 10);
280: hr_utility.set_message(801,'HR_51635_CEL_MANDATORY_INVL');
281: hr_multi_message.add
282: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.MANDATORY');
283:
284: hr_utility.raise_error;
285: end if;
286:
291: hr_utility.set_location(' Leaving:'|| l_proc, 15);
292: exception
293: when app_exception.application_exception then
294: if hr_multi_message.exception_add
295: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.MANDATORY'
296: ) then
297: hr_utility.set_location(' Leaving:'||l_proc,20);
298: raise;
299: end if;
333: --
334: --
335: procedure chk_certification_method
336: (p_competence_element_id
337: in per_competence_elements.competence_element_id%TYPE
338: ,p_effective_date in Date
339: ,p_certification_method
340: in per_competence_elements.certification_method%TYPE
341: ,p_object_version_number
336: (p_competence_element_id
337: in per_competence_elements.competence_element_id%TYPE
338: ,p_effective_date in Date
339: ,p_certification_method
340: in per_competence_elements.certification_method%TYPE
341: ,p_object_version_number
342: in per_competence_elements.object_version_number%TYPE
343: ) is
344: --
338: ,p_effective_date in Date
339: ,p_certification_method
340: in per_competence_elements.certification_method%TYPE
341: ,p_object_version_number
342: in per_competence_elements.object_version_number%TYPE
343: ) is
344: --
345: l_proc varchar2(72):= g_package||'chk_certification_method';
346: l_api_updating boolean;
383: -- Error: Invalid certification_method
384: hr_utility.set_location(l_proc, 10);
385: hr_utility.set_message(801,'HR_51636_CEL_CERTIF_INVL');
386: hr_multi_message.add
387: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD');
388: hr_utility.raise_error;
389: end if;
390: --
391: end if;
395: hr_utility.set_location(' Leaving:'|| l_proc, 15);
396: exception
397: when app_exception.application_exception then
398: if hr_multi_message.exception_add
399: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
400: ) then
401: hr_utility.set_location(' Leaving:'||l_proc,20);
402: raise;
403: end if;
437: --
438: --
439: procedure chk_certification_method_date
440: (p_competence_element_id
441: in per_competence_elements.competence_element_id%TYPE
442: ,p_certification_date
443: in per_competence_elements.certification_date%TYPE
444: ,p_certification_method
445: in per_competence_elements.certification_method%TYPE
439: procedure chk_certification_method_date
440: (p_competence_element_id
441: in per_competence_elements.competence_element_id%TYPE
442: ,p_certification_date
443: in per_competence_elements.certification_date%TYPE
444: ,p_certification_method
445: in per_competence_elements.certification_method%TYPE
446: ,p_object_version_number
447: in per_competence_elements.object_version_number%TYPE
441: in per_competence_elements.competence_element_id%TYPE
442: ,p_certification_date
443: in per_competence_elements.certification_date%TYPE
444: ,p_certification_method
445: in per_competence_elements.certification_method%TYPE
446: ,p_object_version_number
447: in per_competence_elements.object_version_number%TYPE
448: ) is
449: --
443: in per_competence_elements.certification_date%TYPE
444: ,p_certification_method
445: in per_competence_elements.certification_method%TYPE
446: ,p_object_version_number
447: in per_competence_elements.object_version_number%TYPE
448: ) is
449: --
450: l_proc varchar2(72):= g_package||'chk_certification_method';
451: l_api_updating boolean;
456: -- Only proceed with certification method/certification_date validation
457: -- when the multi message list does not already contain an error
458: -- associated with the certification_method.
459: if hr_multi_message.no_exclusive_error
460: ( p_check_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
461: ) then
462: --
463: -- Only proceed with validation if :
464: -- a) The current g_old_rec is current and
486: hr_utility.set_message(801,'HR_51637_CEL_CERF_DATE_METHOD');
487:
488: -- Issue with Error not coming up
489: hr_multi_message.add
490: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE'
491: -- ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
492: );
493:
494: hr_utility.raise_error;
487:
488: -- Issue with Error not coming up
489: hr_multi_message.add
490: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE'
491: -- ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
492: );
493:
494: hr_utility.raise_error;
495:
501: hr_utility.set_location(l_proc, 20);
502: hr_utility.set_message(801,'HR_51629_CEL_CERF_METHOD_DATE');
503: -- Issue with Error not coming up
504: hr_multi_message.add
505: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
506: -- ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE'
507: );
508:
509: hr_utility.raise_error;
502: hr_utility.set_message(801,'HR_51629_CEL_CERF_METHOD_DATE');
503: -- Issue with Error not coming up
504: hr_multi_message.add
505: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
506: -- ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE'
507: );
508:
509: hr_utility.raise_error;
510: end if;
514: end if;
515: exception
516: when app_exception.application_exception then
517: if hr_multi_message.exception_add
518: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
519: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE'
520: ) then
521: hr_utility.set_location(' Leaving:'||l_proc,20);
522: raise;
515: exception
516: when app_exception.application_exception then
517: if hr_multi_message.exception_add
518: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
519: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE'
520: ) then
521: hr_utility.set_location(' Leaving:'||l_proc,20);
522: raise;
523: end if;
556: --
557: procedure chk_next_certification_date
558:
559: (p_competence_element_id
560: in per_competence_elements.competence_element_id%TYPE
561: ,p_certification_date
562: in per_competence_elements.certification_date%TYPE
563: ,p_next_certification_date
564: in per_competence_elements.next_certification_date%TYPE
558:
559: (p_competence_element_id
560: in per_competence_elements.competence_element_id%TYPE
561: ,p_certification_date
562: in per_competence_elements.certification_date%TYPE
563: ,p_next_certification_date
564: in per_competence_elements.next_certification_date%TYPE
565: ,p_object_version_number
566: in per_competence_elements.object_version_number%TYPE
560: in per_competence_elements.competence_element_id%TYPE
561: ,p_certification_date
562: in per_competence_elements.certification_date%TYPE
563: ,p_next_certification_date
564: in per_competence_elements.next_certification_date%TYPE
565: ,p_object_version_number
566: in per_competence_elements.object_version_number%TYPE
567: ,p_effective_date_from
568: in per_competence_elements.effective_date_from%TYPE --added for fix of #731089
562: in per_competence_elements.certification_date%TYPE
563: ,p_next_certification_date
564: in per_competence_elements.next_certification_date%TYPE
565: ,p_object_version_number
566: in per_competence_elements.object_version_number%TYPE
567: ,p_effective_date_from
568: in per_competence_elements.effective_date_from%TYPE --added for fix of #731089
569: ) is
570: --
564: in per_competence_elements.next_certification_date%TYPE
565: ,p_object_version_number
566: in per_competence_elements.object_version_number%TYPE
567: ,p_effective_date_from
568: in per_competence_elements.effective_date_from%TYPE --added for fix of #731089
569: ) is
570: --
571: l_proc varchar2(72):=g_package||'chk_next_certification_date';
572: l_api_updating boolean;
576: begin
577: hr_utility.set_location('Entering:'||l_proc, 1);
578: --
579: if hr_multi_message.no_all_inclusive_error
580: ( p_check_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE' ) then
581: --
582: -- Only Proceed if the value for g_old_rec is current
583: -- and the value for the certification method has changed
584: --
606: --
607: hr_utility.set_location(l_proc,10);
608: hr_utility.set_message(800,'PER_52861_CHK_NEXT_CERT_DATE');
609: hr_multi_message.add
610: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.NEXT_CERTIFICATION_DATE');
611: hr_utility.raise_error;
612: end if;
613: -- Added the code for fix of #731089
614: if p_next_certification_date is not null then
615: if p_next_certification_date < p_effective_date_from THEN
616: hr_utility.set_location(l_proc,12);
617: hr_utility.set_message(800,'PER_289487_CEL_CERT_REV_DATE');
618: hr_multi_message.add
619: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.NEXT_CERTIFICATION_DATE');
620: hr_utility.raise_error;
621: end if;
622: end if;
623: -- Added the code for fix of #731089
630: exception
631: when app_exception.application_exception then
632: if hr_multi_message.exception_add
633: (p_associated_column1 =>
634: 'PER_COMPETENCE_ELEMENTS.NEXT_CERTIFICATION_DATE'
635: ,p_associated_column2 =>
636: 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE'
637: ) then
638: hr_utility.set_location(' Leaving:'||l_proc,20);
632: if hr_multi_message.exception_add
633: (p_associated_column1 =>
634: 'PER_COMPETENCE_ELEMENTS.NEXT_CERTIFICATION_DATE'
635: ,p_associated_column2 =>
636: 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE'
637: ) then
638: hr_utility.set_location(' Leaving:'||l_proc,20);
639: raise;
640: end if;
673: --
674: --
675: procedure chk_competence_type
676: (p_competence_element_id
677: in per_competence_elements.competence_element_id%TYPE
678: ,p_effective_date in Date
679: ,p_competence_type
680: in per_competence_elements.competence_type%TYPE
681: ,p_object_version_number
676: (p_competence_element_id
677: in per_competence_elements.competence_element_id%TYPE
678: ,p_effective_date in Date
679: ,p_competence_type
680: in per_competence_elements.competence_type%TYPE
681: ,p_object_version_number
682: in per_competence_elements.object_version_number%TYPE
683: ) is
684: --
678: ,p_effective_date in Date
679: ,p_competence_type
680: in per_competence_elements.competence_type%TYPE
681: ,p_object_version_number
682: in per_competence_elements.object_version_number%TYPE
683: ) is
684: --
685: l_proc varchar2(72):= g_package||'chk_competence_type';
686:
724: -- Error: Invalid competence_type
725: hr_utility.set_location(l_proc, 10);
726: hr_utility.set_message(801,'HR_51638_CEL_COMP_TYPE_INVL');
727: hr_multi_message.add
728: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_TYPE');
729:
730: hr_utility.raise_error;
731: end if;
732: --
738: exception
739: when app_exception.application_exception then
740: if hr_multi_message.exception_add
741: (p_associated_column1 =>
742: 'PER_COMPETENCE_ELEMENTS.COMPETENCE_TYPE'
743: ) then
744: hr_utility.set_location(' Leaving:'||l_proc,20);
745: raise;
746: end if;
779: --
780: --
781: procedure chk_source_of_proficiency
782: (p_competence_element_id
783: in per_competence_elements.competence_element_id%TYPE
784: ,p_effective_date in Date
785: ,p_source_of_proficiency_level
786: in per_competence_elements.source_of_proficiency_level%TYPE
787: ,p_object_version_number
782: (p_competence_element_id
783: in per_competence_elements.competence_element_id%TYPE
784: ,p_effective_date in Date
785: ,p_source_of_proficiency_level
786: in per_competence_elements.source_of_proficiency_level%TYPE
787: ,p_object_version_number
788: in per_competence_elements.object_version_number%TYPE
789: ) is
790: --
784: ,p_effective_date in Date
785: ,p_source_of_proficiency_level
786: in per_competence_elements.source_of_proficiency_level%TYPE
787: ,p_object_version_number
788: in per_competence_elements.object_version_number%TYPE
789: ) is
790: --
791: l_proc varchar2(72):=
792:
834:
835: hr_utility.set_location(l_proc, 10);
836: hr_utility.set_message(801,'HR_51639_CEL_SOURCE_PROF_INVL');
837: hr_multi_message.add
838: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.SOURCE_OF_PROFICIENCY_LEVEL');
839:
840: hr_utility.raise_error;
841: end if;
842: --
848: exception
849: when app_exception.application_exception then
850: if hr_multi_message.exception_add
851: (p_associated_column1 =>
852: 'PER_COMPETENCE_ELEMENTS.SOURCE_OF_PROFICIENCY_LEVEL'
853: ) then
854: hr_utility.set_location(' Leaving:'||l_proc,20);
855: raise;
856: end if;
893: --
894: --
895: procedure chk_type
896: (p_competence_element_id
897: in per_competence_elements.competence_element_id%TYPE
898: ,p_effective_date in Date
899: ,p_type in per_competence_elements.type%TYPE
900: ,p_object_version_number
901: in per_competence_elements.object_version_number%TYPE
895: procedure chk_type
896: (p_competence_element_id
897: in per_competence_elements.competence_element_id%TYPE
898: ,p_effective_date in Date
899: ,p_type in per_competence_elements.type%TYPE
900: ,p_object_version_number
901: in per_competence_elements.object_version_number%TYPE
902: ) is
903: --
897: in per_competence_elements.competence_element_id%TYPE
898: ,p_effective_date in Date
899: ,p_type in per_competence_elements.type%TYPE
900: ,p_object_version_number
901: in per_competence_elements.object_version_number%TYPE
902: ) is
903: --
904: l_proc varchar2(72):= g_package||'chk_type';
905: l_api_updating boolean;
943: -- Error: Invalid competence_type
944: hr_utility.set_location(l_proc, 10);
945: hr_utility.set_message(801,'HR_51641_CEL_COMP_ELTP_INVL');
946: hr_multi_message.add
947: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_TYPE');
948: hr_utility.raise_error;
949:
950: end if;
951: --
957: exception
958: when app_exception.application_exception then
959: if hr_multi_message.exception_add
960: (p_associated_column1 =>
961: 'PER_COMPETENCE_ELEMENTS.TYPE'
962: ) then
963: hr_utility.set_location(' Leaving:'||l_proc,20);
964: raise;
965: end if;
1052: --
1053: --
1054: procedure chk_foreign_keys
1055: (p_competence_element_id
1056: in per_competence_elements.competence_element_id%TYPE
1057: ,p_job_id
1058: in per_competence_elements.job_id%TYPE
1059: ,p_valid_grade_id
1060: in per_competence_elements.valid_grade_id%TYPE
1054: procedure chk_foreign_keys
1055: (p_competence_element_id
1056: in per_competence_elements.competence_element_id%TYPE
1057: ,p_job_id
1058: in per_competence_elements.job_id%TYPE
1059: ,p_valid_grade_id
1060: in per_competence_elements.valid_grade_id%TYPE
1061: ,p_activity_version_id
1062: in per_competence_elements.activity_version_id%TYPE
1056: in per_competence_elements.competence_element_id%TYPE
1057: ,p_job_id
1058: in per_competence_elements.job_id%TYPE
1059: ,p_valid_grade_id
1060: in per_competence_elements.valid_grade_id%TYPE
1061: ,p_activity_version_id
1062: in per_competence_elements.activity_version_id%TYPE
1063: ,p_person_id
1064: in per_competence_elements.person_id%TYPE
1058: in per_competence_elements.job_id%TYPE
1059: ,p_valid_grade_id
1060: in per_competence_elements.valid_grade_id%TYPE
1061: ,p_activity_version_id
1062: in per_competence_elements.activity_version_id%TYPE
1063: ,p_person_id
1064: in per_competence_elements.person_id%TYPE
1065: ,p_position_id
1066: in per_competence_elements.position_id%TYPE
1060: in per_competence_elements.valid_grade_id%TYPE
1061: ,p_activity_version_id
1062: in per_competence_elements.activity_version_id%TYPE
1063: ,p_person_id
1064: in per_competence_elements.person_id%TYPE
1065: ,p_position_id
1066: in per_competence_elements.position_id%TYPE
1067: ,p_organization_id
1068: in per_competence_elements.organization_id%TYPE
1062: in per_competence_elements.activity_version_id%TYPE
1063: ,p_person_id
1064: in per_competence_elements.person_id%TYPE
1065: ,p_position_id
1066: in per_competence_elements.position_id%TYPE
1067: ,p_organization_id
1068: in per_competence_elements.organization_id%TYPE
1069: ,p_assessment_id
1070: in per_competence_elements.assessment_id%TYPE
1064: in per_competence_elements.person_id%TYPE
1065: ,p_position_id
1066: in per_competence_elements.position_id%TYPE
1067: ,p_organization_id
1068: in per_competence_elements.organization_id%TYPE
1069: ,p_assessment_id
1070: in per_competence_elements.assessment_id%TYPE
1071: ,p_assessment_type_id
1072: in per_competence_elements.assessment_type_id%TYPE
1066: in per_competence_elements.position_id%TYPE
1067: ,p_organization_id
1068: in per_competence_elements.organization_id%TYPE
1069: ,p_assessment_id
1070: in per_competence_elements.assessment_id%TYPE
1071: ,p_assessment_type_id
1072: in per_competence_elements.assessment_type_id%TYPE
1073: ,p_competence_id
1074: in per_competence_elements.competence_id%TYPE
1068: in per_competence_elements.organization_id%TYPE
1069: ,p_assessment_id
1070: in per_competence_elements.assessment_id%TYPE
1071: ,p_assessment_type_id
1072: in per_competence_elements.assessment_type_id%TYPE
1073: ,p_competence_id
1074: in per_competence_elements.competence_id%TYPE
1075: ,p_proficiency_level_id
1076: in per_competence_elements.proficiency_level_id%TYPE
1070: in per_competence_elements.assessment_id%TYPE
1071: ,p_assessment_type_id
1072: in per_competence_elements.assessment_type_id%TYPE
1073: ,p_competence_id
1074: in per_competence_elements.competence_id%TYPE
1075: ,p_proficiency_level_id
1076: in per_competence_elements.proficiency_level_id%TYPE
1077: ,p_high_proficiency_level_id
1078: in per_competence_elements.high_proficiency_level_id%TYPE
1072: in per_competence_elements.assessment_type_id%TYPE
1073: ,p_competence_id
1074: in per_competence_elements.competence_id%TYPE
1075: ,p_proficiency_level_id
1076: in per_competence_elements.proficiency_level_id%TYPE
1077: ,p_high_proficiency_level_id
1078: in per_competence_elements.high_proficiency_level_id%TYPE
1079: ,p_rating_level_id
1080: in per_competence_elements.rating_level_id%TYPE
1074: in per_competence_elements.competence_id%TYPE
1075: ,p_proficiency_level_id
1076: in per_competence_elements.proficiency_level_id%TYPE
1077: ,p_high_proficiency_level_id
1078: in per_competence_elements.high_proficiency_level_id%TYPE
1079: ,p_rating_level_id
1080: in per_competence_elements.rating_level_id%TYPE
1081: ,p_weighting_level_id
1082: in per_competence_elements.weighting_level_id%TYPE
1076: in per_competence_elements.proficiency_level_id%TYPE
1077: ,p_high_proficiency_level_id
1078: in per_competence_elements.high_proficiency_level_id%TYPE
1079: ,p_rating_level_id
1080: in per_competence_elements.rating_level_id%TYPE
1081: ,p_weighting_level_id
1082: in per_competence_elements.weighting_level_id%TYPE
1083: ,p_parent_competence_element_id
1084: in per_competence_elements.parent_competence_element_id%TYPE
1078: in per_competence_elements.high_proficiency_level_id%TYPE
1079: ,p_rating_level_id
1080: in per_competence_elements.rating_level_id%TYPE
1081: ,p_weighting_level_id
1082: in per_competence_elements.weighting_level_id%TYPE
1083: ,p_parent_competence_element_id
1084: in per_competence_elements.parent_competence_element_id%TYPE
1085: ,p_business_group_id
1086: in per_competence_elements.business_group_id%TYPE
1080: in per_competence_elements.rating_level_id%TYPE
1081: ,p_weighting_level_id
1082: in per_competence_elements.weighting_level_id%TYPE
1083: ,p_parent_competence_element_id
1084: in per_competence_elements.parent_competence_element_id%TYPE
1085: ,p_business_group_id
1086: in per_competence_elements.business_group_id%TYPE
1087: ,p_enterprise_id
1088: in per_competence_elements.enterprise_id%TYPE
1082: in per_competence_elements.weighting_level_id%TYPE
1083: ,p_parent_competence_element_id
1084: in per_competence_elements.parent_competence_element_id%TYPE
1085: ,p_business_group_id
1086: in per_competence_elements.business_group_id%TYPE
1087: ,p_enterprise_id
1088: in per_competence_elements.enterprise_id%TYPE
1089: ,p_object_version_number
1090: in per_competence_elements.object_version_number%TYPE
1084: in per_competence_elements.parent_competence_element_id%TYPE
1085: ,p_business_group_id
1086: in per_competence_elements.business_group_id%TYPE
1087: ,p_enterprise_id
1088: in per_competence_elements.enterprise_id%TYPE
1089: ,p_object_version_number
1090: in per_competence_elements.object_version_number%TYPE
1091: ,p_effective_date_from
1092: per_competence_elements.effective_date_from%TYPE
1086: in per_competence_elements.business_group_id%TYPE
1087: ,p_enterprise_id
1088: in per_competence_elements.enterprise_id%TYPE
1089: ,p_object_version_number
1090: in per_competence_elements.object_version_number%TYPE
1091: ,p_effective_date_from
1092: per_competence_elements.effective_date_from%TYPE
1093: ,p_effective_date_to
1094: per_competence_elements.effective_date_to%TYPE
1088: in per_competence_elements.enterprise_id%TYPE
1089: ,p_object_version_number
1090: in per_competence_elements.object_version_number%TYPE
1091: ,p_effective_date_from
1092: per_competence_elements.effective_date_from%TYPE
1093: ,p_effective_date_to
1094: per_competence_elements.effective_date_to%TYPE
1095: ,p_effective_date date
1096: ,p_type per_competence_elements.type%TYPE
1090: in per_competence_elements.object_version_number%TYPE
1091: ,p_effective_date_from
1092: per_competence_elements.effective_date_from%TYPE
1093: ,p_effective_date_to
1094: per_competence_elements.effective_date_to%TYPE
1095: ,p_effective_date date
1096: ,p_type per_competence_elements.type%TYPE
1097: ,p_party_id per_competence_elements.party_id%TYPE -- HR/TCA merge
1098: ,p_qualification_type_id per_competence_elements.qualification_type_id%TYPE
1092: per_competence_elements.effective_date_from%TYPE
1093: ,p_effective_date_to
1094: per_competence_elements.effective_date_to%TYPE
1095: ,p_effective_date date
1096: ,p_type per_competence_elements.type%TYPE
1097: ,p_party_id per_competence_elements.party_id%TYPE -- HR/TCA merge
1098: ,p_qualification_type_id per_competence_elements.qualification_type_id%TYPE
1099: )is
1100: --
1093: ,p_effective_date_to
1094: per_competence_elements.effective_date_to%TYPE
1095: ,p_effective_date date
1096: ,p_type per_competence_elements.type%TYPE
1097: ,p_party_id per_competence_elements.party_id%TYPE -- HR/TCA merge
1098: ,p_qualification_type_id per_competence_elements.qualification_type_id%TYPE
1099: )is
1100: --
1101: l_proc varchar2(72) := g_package||'chk_foreign_keys';
1094: per_competence_elements.effective_date_to%TYPE
1095: ,p_effective_date date
1096: ,p_type per_competence_elements.type%TYPE
1097: ,p_party_id per_competence_elements.party_id%TYPE -- HR/TCA merge
1098: ,p_qualification_type_id per_competence_elements.qualification_type_id%TYPE
1099: )is
1100: --
1101: l_proc varchar2(72) := g_package||'chk_foreign_keys';
1102: l_business_group_id per_jobs.business_group_id%TYPE;
1106: l_end_date_2 per_jobs.date_to%TYPE;
1107: l_date_of_birth per_people_f.date_of_birth%TYPE;
1108: l_exist varchar2(1);
1109: l_api_updating boolean;
1110: l_party_id per_competence_elements.party_id%TYPE; -- HR/TCA merge
1111: l_qualification_type_id per_competence_elements.qualification_type_id%TYPE;
1112: --
1113: --
1114: -- cursor to check that the job_id exists.
1107: l_date_of_birth per_people_f.date_of_birth%TYPE;
1108: l_exist varchar2(1);
1109: l_api_updating boolean;
1110: l_party_id per_competence_elements.party_id%TYPE; -- HR/TCA merge
1111: l_qualification_type_id per_competence_elements.qualification_type_id%TYPE;
1112: --
1113: --
1114: -- cursor to check that the job_id exists.
1115: --
1253: -- cursor to check that the parent_comp_element_id is valid
1254: --
1255: cursor csr_valid_comp_id is
1256: select business_group_id
1257: from per_competence_elements
1258: where p_parent_competence_element_id =
1259: competence_element_id;
1260:
1261: --
1331: fetch csr_valid_person_id into l_business_group_id,l_start_date,l_end_date;
1332: if csr_valid_person_id%notfound then
1333: --hr_utility.set_location(l_proc, 10);
1334: close csr_valid_person_id;
1335: --per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK7');
1336: else
1337:
1338: close csr_valid_person_id;
1339: if
1341: --
1342: -- The person_id exists in a different_business_group_id.
1343: --
1344: hr_utility.set_location(l_proc, 15);
1345: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1346: elsif invalid_person_dates(p_effective_date_from,p_effective_date_to) then
1347: --
1348: -- The person_id is not date valid
1349: -- Part of fix for bug 572277
1347: --
1348: -- The person_id is not date valid
1349: -- Part of fix for bug 572277
1350: --
1351: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK19');
1352: end if;
1353: end if;
1354: --
1355: end if;
1367: hr_utility.set_location(l_proc, 45);
1368: hr_utility.set_message(801,'HR_51701_CEL_ACTIVE_ID_INVL');
1369: hr_multi_message.add
1370: (p_associated_column1 =>
1371: 'PER_COMPETENCE_ELEMENTS.ACTIVITY_VERSION_ID'
1372: );
1373: close csr_valid_activity_id;
1374: else
1375: close csr_valid_activity_id;
1377: --
1378: -- The activity_version exists in a different_business_group_id.
1379: --
1380: hr_utility.set_location(l_proc, 50);
1381: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1382: --
1383: elsif l_start_date > p_effective_date_from or
1384: l_end_date < nvl(p_effective_date_to, l_end_date) then
1385: --
1387: --
1388: hr_utility.set_message(810,'OTA_13673_TAV_COMPETENCY_DATES');
1389: hr_multi_message.add
1390: (p_associated_column1 =>
1391: 'PER_COMPETENCE_ELEMENTS.ACTIVITY_VERSION_ID'
1392: ,p_associated_column2 =>
1393: 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
1394: ,p_associated_column3 =>
1395: 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
1389: hr_multi_message.add
1390: (p_associated_column1 =>
1391: 'PER_COMPETENCE_ELEMENTS.ACTIVITY_VERSION_ID'
1392: ,p_associated_column2 =>
1393: 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
1394: ,p_associated_column3 =>
1395: 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
1396: );
1397: end if;
1391: 'PER_COMPETENCE_ELEMENTS.ACTIVITY_VERSION_ID'
1392: ,p_associated_column2 =>
1393: 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
1394: ,p_associated_column3 =>
1395: 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
1396: );
1397: end if;
1398: end if;
1399: --
1407: fetch csr_valid_job_id into l_business_group_id, l_start_date,l_end_date;
1408: if csr_valid_job_id%notfound then
1409: hr_utility.set_location(l_proc, 85);
1410: close csr_valid_job_id;
1411: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK8');
1412: else
1413: close csr_valid_job_id;
1414: if
1415: p_business_group_id <> l_business_group_id then
1417: --
1418: -- The job_id exists in a different_business_group_id.
1419: --
1420: hr_utility.set_location(l_proc, 90);
1421: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1422: elsif (p_effective_date_from < l_start_date) OR
1423: (nvl(p_effective_date_to,hr_api.g_eot) >
1424: nvl(l_end_date,hr_api.g_eot)) THEN
1425: --
1425: --
1426: -- The job_id is not date valid
1427: -- Part of fix for bug 572277
1428: --
1429: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK20');
1430:
1431:
1432: end if;
1433: end if;
1445: close csr_valid_grade_id;
1446: hr_utility.set_message(800, 'HR_52372_CEL_INVL_GRD_ID');
1447: hr_utility.set_location(l_proc,83);
1448: hr_multi_message.add
1449: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID'
1450: );
1451: else
1452: close csr_valid_grade_id;
1453: if
1456: --
1457: -- The valid_grade_id exists in a different_business_group_id.
1458: --
1459: hr_utility.set_location(l_proc, 84);
1460: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1461: elsif (p_effective_date_from < l_start_date) OR
1462: (nvl(p_effective_date_to,hr_api.g_eot) >
1463: nvl(l_end_date,hr_api.g_eot)) THEN
1464: --
1464: --
1465: -- The valid_grade_id is not date valid
1466: -- Part of fix for bug 572277
1467: --
1468: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK22');
1469:
1470: end if;
1471: end if;
1472: --
1482: fetch csr_valid_position_id into l_business_group_id,l_start_date,l_end_date;
1483: if csr_valid_position_id%notfound then
1484: hr_utility.set_location(l_proc, 100);
1485: close csr_valid_position_id;
1486: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK9');
1487: else
1488: close csr_valid_position_id;
1489: if
1490: p_business_group_id <> l_business_group_id then
1491: --
1492: -- The position_id exists in a different_business_group_id.
1493: --
1494: hr_utility.set_location(l_proc, 105);
1495: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1496: elsif (p_effective_date_from < l_start_date) OR
1497: (nvl(p_effective_date_to,hr_api.g_eot) >
1498: nvl(l_end_date,hr_api.g_eot)) THEN
1499: --
1499: --
1500: -- The position_id is not date valid
1501: -- Part of fix for bug 572277
1502: --
1503: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK20');
1504:
1505: end if;
1506: end if;
1507: --
1515: fetch csr_valid_organization_id into l_business_group_id,l_start_date,l_end_date;
1516: if csr_valid_organization_id%notfound then
1517: hr_utility.set_location(l_proc, 115);
1518: close csr_valid_organization_id;
1519: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK10');
1520: else
1521: close csr_valid_organization_id;
1522: if
1523: p_business_group_id <> l_business_group_id then
1525: -- The organization_id exists in a different_business_group_id.
1526: --
1527: hr_utility.set_location(l_proc, 120);
1528:
1529: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1530: elsif (p_effective_date_from < l_start_date) OR
1531: (nvl(p_effective_date_to,hr_api.g_eot) >
1532: nvl(l_end_date,hr_api.g_eot)) THEN
1533: --
1533: --
1534: -- The organization_id is not date valid
1535: -- Part of fix for bug 572277
1536: --
1537: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK20');
1538: end if;
1539: --
1540: end if;
1541: --
1550:
1551: if csr_valid_assessment_id%notfound then
1552: hr_utility.set_location(l_proc, 130);
1553: close csr_valid_assessment_id;
1554: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK14');
1555: else
1556: close csr_valid_assessment_id;
1557: if
1558: p_business_group_id <> l_business_group_id then
1559: --
1560: -- The assessment_id exists in a different_business_group_id.
1561: --
1562: hr_utility.set_location(l_proc, 135);
1563: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1564:
1565: end if;
1566: --
1567: end if;
1578: if csr_valid_assessment_type_id%notfound then
1579:
1580: hr_utility.set_location(l_proc, 152);
1581: close csr_valid_assessment_type_id;
1582: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK15');
1583: else
1584: close csr_valid_assessment_type_id;
1585: if
1586: p_business_group_id <> l_business_group_id then
1587: --
1588: -- The assessment_type_id exists in a different_business_group_id.
1589: --
1590: hr_utility.set_location(l_proc, 153);
1591: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1592: end if;
1593:
1594: --
1595: end if;
1604: if csr_valid_comp_id%notfound then
1605: hr_utility.set_location(l_proc, 160);
1606: close csr_valid_comp_id;
1607:
1608: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK18');
1609: else
1610: close csr_valid_comp_id;
1611: if
1612: p_business_group_id <> l_business_group_id then
1614: -- The parent_comp_element_id exists in
1615: -- a different_business_group_id.
1616: --
1617: hr_utility.set_location(l_proc, 165);
1618: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1619: end if;
1620: --
1621:
1622: end if;
1632: fetch csr_val_prof_id into l_business_group_id;
1633: if csr_val_prof_id%notfound then
1634: hr_utility.set_location(l_proc, 210);
1635: close csr_val_prof_id;
1636: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK6');
1637: else
1638:
1639: close csr_val_prof_id;
1640: if
1643: -- The high_proficiency_level_id exists in a
1644: -- different_business_group_id.
1645: --
1646: hr_utility.set_location(l_proc, 215);
1647: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1648: end if;
1649: end if;
1650: --
1651: end if;
1660: fetch csr_val_prof_id into l_business_group_id;
1661: if csr_val_prof_id%notfound then
1662: hr_utility.set_location(l_proc, 225);
1663: close csr_val_prof_id;
1664: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK5');
1665: else
1666:
1667: hr_utility.set_location(l_proc, 226);
1668: close csr_val_prof_id;
1674: -- The proficiency_level_id exists
1675: -- in a different_business_group_id.
1676: --
1677: hr_utility.set_location(l_proc, 230);
1678: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1679: end if;
1680: end if;
1681:
1682: --
1690: fetch csr_val_prof_id into l_business_group_id;
1691: if csr_val_prof_id%notfound then
1692: hr_utility.set_location(l_proc, 240);
1693: close csr_val_prof_id;
1694: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK17');
1695:
1696: else
1697: close csr_val_prof_id;
1698: if
1701: -- The weighting_level_id exists in
1702: -- a different_business_group_id.
1703: --
1704: hr_utility.set_location(l_proc, 245);
1705: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1706: end if;
1707: end if;
1708: --
1709:
1717: fetch csr_val_prof_id into l_business_group_id;
1718: if csr_val_prof_id%notfound then
1719: hr_utility.set_location(l_proc, 255);
1720: close csr_val_prof_id;
1721: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK16');
1722: else
1723:
1724: close csr_val_prof_id;
1725: -- ngundura changes done for pa requirements
1728: --
1729: -- The rating_level_id exists in a different_business_group_id.
1730: --
1731: hr_utility.set_location(l_proc, 260);
1732: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1733: end if;
1734: end if;
1735: --
1736: end if;
1745: open csr_usage_competence_id;
1746: fetch csr_usage_competence_id into l_business_group_id;
1747: if csr_usage_competence_id%notfound then
1748: close csr_usage_competence_id;
1749: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK1');
1750: else
1751: close csr_usage_competence_id;
1752: -- ngundura changes done for pa requirements
1753: -- added the AND condition in the following if
1755: p_business_group_id <> l_business_group_id and l_business_group_id is not null then
1756: --
1757: -- The competence_id exists in a different_business_group_id.
1758: --
1759: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1760: end if;
1761: end if;
1762: else
1763: open csr_valid_competence_id;
1766: hr_utility.set_location(l_proc, 267);
1767: if csr_valid_competence_id%notfound then
1768: hr_utility.set_location(l_proc, 270);
1769: close csr_valid_competence_id;
1770: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK1');
1771: else
1772: hr_utility.set_location(l_proc, 271);
1773: close csr_valid_competence_id;
1774: -- ngundura changes done for pa requirement
1778: --
1779: -- The competence_id exists in a different_business_group_id.
1780: --
1781: hr_utility.set_location(l_proc, 275);
1782: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1783: elsif (nvl(p_effective_date_from,hr_api.g_eot) <
1784: nvl(l_start_date,hr_api.g_eot)) OR
1785: (nvl(p_effective_date_to,nvl(l_end_date,hr_api.g_eot)) > nvl(l_end_date,hr_api.g_eot)) THEN
1786: --
1786: --
1787: -- The competence_id is not date valid
1788: -- Part of fix for bug 572277
1789: --
1790: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK21');
1791:
1792: end if;
1793: end if;
1794: --
1810: fetch csr_val_prof_id into l_business_group_id;
1811: if csr_val_prof_id%notfound then
1812: hr_utility.set_location(l_proc, 285);
1813: close csr_val_prof_id;
1814: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK6');
1815: else
1816: close csr_val_prof_id;
1817: -- ngundura changes for pa requirements
1818: if
1822: -- different_business_group_id.
1823:
1824: --
1825: hr_utility.set_location(l_proc, 290);
1826: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1827: end if;
1828: end if;
1829: --
1830: end if;
1838: fetch csr_val_prof_id into l_business_group_id;
1839: if csr_val_prof_id%notfound then
1840: hr_utility.set_location(l_proc, 300);
1841: close csr_val_prof_id;
1842: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK5');
1843: else
1844: close csr_val_prof_id;
1845: -- ngundura changes for pa requirements
1846: -- added the AND condition in the following if
1851: -- different_business_group_id.
1852: --
1853:
1854: hr_utility.set_location(l_proc, 305);
1855: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1856: end if;
1857: end if;
1858: --
1859: end if;
1881: --
1882: -- The person_id is not date valid
1883: -- Part of fix for bug 572277
1884: --
1885: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK19');
1886: end if;
1887: --
1888: end if;
1889: --
1903: --
1904: hr_utility.set_message(810,'OTA_13673_TAV_COMPETENCY_DATES');
1905: hr_multi_message.add
1906: (p_associated_column1 =>
1907: 'PER_COMPETENCE_ELEMENTS.ACTIVITY_VERSION_ID'
1908: ,p_associated_column2 =>
1909: 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
1910: ,p_associated_column3 =>
1911: 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
1905: hr_multi_message.add
1906: (p_associated_column1 =>
1907: 'PER_COMPETENCE_ELEMENTS.ACTIVITY_VERSION_ID'
1908: ,p_associated_column2 =>
1909: 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
1910: ,p_associated_column3 =>
1911: 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
1912: );
1913: end if;
1907: 'PER_COMPETENCE_ELEMENTS.ACTIVITY_VERSION_ID'
1908: ,p_associated_column2 =>
1909: 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
1910: ,p_associated_column3 =>
1911: 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
1912: );
1913: end if;
1914: --
1915: end if;
1927: --
1928: -- The job_id is not date valid
1929: -- Part of fix for bug 572277
1930: --
1931: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK20');
1932: end if;
1933: --
1934: end if;
1935: --
1946: --
1947: -- The valid_grade_id is not date valid
1948: -- Part of fix for bug 572277
1949: --
1950: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK22');
1951:
1952: end if;
1953: --
1954: end if;
1968: --
1969: -- The position_id is not date valid
1970: -- Part of fix for bug 572277
1971: --
1972: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK20');
1973:
1974: end if;
1975: --
1976: end if;
1988: --
1989: -- The organization_id is not date valid
1990: -- Part of fix for bug 572277
1991: --
1992: per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK20');
1993: end if;
1994: --
1995: end if;
1996: --
2008: --
2009: -- The competence_id is not date valid
2010: -- Part of fix for bug 572277
2011: --
2012: Per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK21');
2013:
2014: end if;
2015: --
2016: end if;
2024: fetch csr_valid_qualification_type into l_qualification_type_id;
2025:
2026: if csr_valid_qualification_type%NOTFOUND then
2027: close csr_valid_qualification_type;
2028: Per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK23');
2029: else
2030: close csr_valid_qualification_type;
2031: end if;
2032: --
2072: ,p_effective_date in date
2073: )is
2074: --
2075: l_proc varchar2(72) := g_package||'chk_party_id';
2076: l_party_id per_competence_elements.party_id%TYPE;
2077: l_party_id2 per_competence_elements.party_id%TYPE;
2078: --
2079: --
2080: -- cursor to check that the party_id maches person_id
2073: )is
2074: --
2075: l_proc varchar2(72) := g_package||'chk_party_id';
2076: l_party_id per_competence_elements.party_id%TYPE;
2077: l_party_id2 per_competence_elements.party_id%TYPE;
2078: --
2079: --
2080: -- cursor to check that the party_id maches person_id
2081: --
2107: hr_utility.set_message(800, 'HR_289343_PERSONPARTY_MISMATCH');
2108: hr_utility.set_location(l_proc,30);
2109: hr_multi_message.add
2110: (p_associated_column1 =>
2111: 'PER_COMPETENCE_ELEMENTS.PARTY_ID'
2112: ,p_associated_column2 =>
2113: 'PER_COMPETENCE_ELEMENTS.PERSON_ID'
2114: );
2115: end if;
2109: hr_multi_message.add
2110: (p_associated_column1 =>
2111: 'PER_COMPETENCE_ELEMENTS.PARTY_ID'
2112: ,p_associated_column2 =>
2113: 'PER_COMPETENCE_ELEMENTS.PERSON_ID'
2114: );
2115: end if;
2116: --
2117: else -- if party_id is null
2132: hr_utility.set_message(800, 'PER_289342_PARTY_ID_INVALID');
2133: hr_utility.set_location(l_proc,70);
2134: hr_multi_message.add
2135: (p_associated_column1 =>
2136: 'PER_COMPETENCE_ELEMENTS.PARTY_ID'
2137: );
2138: else
2139: --
2140: close csr_valid_party_id;
2186: --
2187: --
2188: procedure chk_proficiency_level_id
2189: (p_competence_element_id
2190: in per_competence_elements.competence_element_id%TYPE
2191: ,p_business_group_id
2192: in per_competence_elements.business_group_id%TYPE
2193: ,p_object_version_number
2194: in per_competence_elements.object_version_number%TYPE
2188: procedure chk_proficiency_level_id
2189: (p_competence_element_id
2190: in per_competence_elements.competence_element_id%TYPE
2191: ,p_business_group_id
2192: in per_competence_elements.business_group_id%TYPE
2193: ,p_object_version_number
2194: in per_competence_elements.object_version_number%TYPE
2195: ,p_proficiency_level_id
2196: in per_competence_elements.proficiency_level_id%TYPE
2190: in per_competence_elements.competence_element_id%TYPE
2191: ,p_business_group_id
2192: in per_competence_elements.business_group_id%TYPE
2193: ,p_object_version_number
2194: in per_competence_elements.object_version_number%TYPE
2195: ,p_proficiency_level_id
2196: in per_competence_elements.proficiency_level_id%TYPE
2197: ,p_high_proficiency_level_id
2198: in per_competence_elements.high_proficiency_level_id%TYPE
2192: in per_competence_elements.business_group_id%TYPE
2193: ,p_object_version_number
2194: in per_competence_elements.object_version_number%TYPE
2195: ,p_proficiency_level_id
2196: in per_competence_elements.proficiency_level_id%TYPE
2197: ,p_high_proficiency_level_id
2198: in per_competence_elements.high_proficiency_level_id%TYPE
2199: ,p_competence_id
2200: in per_competence_elements.competence_id%TYPE
2194: in per_competence_elements.object_version_number%TYPE
2195: ,p_proficiency_level_id
2196: in per_competence_elements.proficiency_level_id%TYPE
2197: ,p_high_proficiency_level_id
2198: in per_competence_elements.high_proficiency_level_id%TYPE
2199: ,p_competence_id
2200: in per_competence_elements.competence_id%TYPE
2201: ,p_party_id
2202: in per_competence_elements.party_id%TYPE
2196: in per_competence_elements.proficiency_level_id%TYPE
2197: ,p_high_proficiency_level_id
2198: in per_competence_elements.high_proficiency_level_id%TYPE
2199: ,p_competence_id
2200: in per_competence_elements.competence_id%TYPE
2201: ,p_party_id
2202: in per_competence_elements.party_id%TYPE
2203: ) is
2204: --
2198: in per_competence_elements.high_proficiency_level_id%TYPE
2199: ,p_competence_id
2200: in per_competence_elements.competence_id%TYPE
2201: ,p_party_id
2202: in per_competence_elements.party_id%TYPE
2203: ) is
2204: --
2205: l_proc varchar2(72):=
2206: g_package||'chk_proficiency_level_id';
2278: hr_utility.set_message(801,'HR_51616_CEL_HG_PROF_ID_INVL');
2279: hr_utility.set_location(l_proc,15);
2280: hr_multi_message.add
2281: (p_associated_column1 =>
2282: 'PER_COMPETENCE_ELEMENTS.HIGH_PROFICIENCY_LEVEL_ID'
2283: ,p_associated_column2 =>
2284: 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2285: );
2286: else
2280: hr_multi_message.add
2281: (p_associated_column1 =>
2282: 'PER_COMPETENCE_ELEMENTS.HIGH_PROFICIENCY_LEVEL_ID'
2283: ,p_associated_column2 =>
2284: 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2285: );
2286: else
2287: hr_utility.set_message(801,'HR_51615_CEL_PROF_ID_INVL');
2288: hr_utility.set_location(l_proc,20);
2287: hr_utility.set_message(801,'HR_51615_CEL_PROF_ID_INVL');
2288: hr_utility.set_location(l_proc,20);
2289: hr_multi_message.add
2290: (p_associated_column1 =>
2291: 'PER_COMPETENCE_ELEMENTS.PROFICIENCY_LEVEL_ID'
2292: ,p_associated_column2 =>
2293: 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2294: );
2295: end if;
2289: hr_multi_message.add
2290: (p_associated_column1 =>
2291: 'PER_COMPETENCE_ELEMENTS.PROFICIENCY_LEVEL_ID'
2292: ,p_associated_column2 =>
2293: 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2294: );
2295: end if;
2296: --
2297: hr_utility.set_location(l_proc,25);
2345: hr_utility.set_location(l_proc,6);
2346: hr_utility.set_message(801,'HR_51726_CEL_PROF_HG_PROF');
2347: hr_multi_message.add
2348: (p_associated_column1 =>
2349: 'PER_COMPETENCE_ELEMENTS.HIGH_PROFICIENCY_LEVEL_ID'
2350: ,p_associated_column2 =>
2351: 'PER_COMPETENCE_ELEMENTS.PROFICIENCY_LEVEL_ID'
2352: );
2353: else
2347: hr_multi_message.add
2348: (p_associated_column1 =>
2349: 'PER_COMPETENCE_ELEMENTS.HIGH_PROFICIENCY_LEVEL_ID'
2350: ,p_associated_column2 =>
2351: 'PER_COMPETENCE_ELEMENTS.PROFICIENCY_LEVEL_ID'
2352: );
2353: else
2354: --
2355: -- validate competence_id against the proficiency_levels
2362: hr_utility.set_location(l_proc,10);
2363: hr_utility.set_message(801,'HR_51642_COMP_ID_MANDATORY');
2364: hr_multi_message.add
2365: (p_associated_column1 =>
2366: 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2367: );
2368: --
2369: else -- if p_competence_id is not null
2370: --
2394: hr_utility.set_location(l_proc,25);
2395: hr_utility.set_message(801,'HR_51644_CEL_PROF_VAL_ERROR');
2396: hr_multi_message.add
2397: (p_associated_column1 =>
2398: 'PER_COMPETENCE_ELEMENTS.HIGH_PROFICIENCY_LEVEL_ID'
2399: ,p_associated_column2 =>
2400: 'PER_COMPETENCE_ELEMENTS.PROFICIENCY_LEVEL_ID'
2401: );
2402: end if;
2396: hr_multi_message.add
2397: (p_associated_column1 =>
2398: 'PER_COMPETENCE_ELEMENTS.HIGH_PROFICIENCY_LEVEL_ID'
2399: ,p_associated_column2 =>
2400: 'PER_COMPETENCE_ELEMENTS.PROFICIENCY_LEVEL_ID'
2401: );
2402: end if;
2403: end if;
2404: --
2452: --
2453: --
2454: procedure chk_rating_weighting_id
2455: (p_competence_element_id
2456: in per_competence_elements.competence_element_id%TYPE
2457: ,p_business_group_id
2458: in per_competence_elements.business_group_id%TYPE
2459: ,p_object_version_number
2460: in per_competence_elements.object_version_number%TYPE
2454: procedure chk_rating_weighting_id
2455: (p_competence_element_id
2456: in per_competence_elements.competence_element_id%TYPE
2457: ,p_business_group_id
2458: in per_competence_elements.business_group_id%TYPE
2459: ,p_object_version_number
2460: in per_competence_elements.object_version_number%TYPE
2461: ,p_rating_level_id
2462: in per_competence_elements.rating_level_id%TYPE
2456: in per_competence_elements.competence_element_id%TYPE
2457: ,p_business_group_id
2458: in per_competence_elements.business_group_id%TYPE
2459: ,p_object_version_number
2460: in per_competence_elements.object_version_number%TYPE
2461: ,p_rating_level_id
2462: in per_competence_elements.rating_level_id%TYPE
2463: ,p_weighting_level_id
2464: in per_competence_elements.weighting_level_id%TYPE
2458: in per_competence_elements.business_group_id%TYPE
2459: ,p_object_version_number
2460: in per_competence_elements.object_version_number%TYPE
2461: ,p_rating_level_id
2462: in per_competence_elements.rating_level_id%TYPE
2463: ,p_weighting_level_id
2464: in per_competence_elements.weighting_level_id%TYPE
2465: ,p_assessment_id
2466: in per_competence_elements.assessment_id%TYPE
2460: in per_competence_elements.object_version_number%TYPE
2461: ,p_rating_level_id
2462: in per_competence_elements.rating_level_id%TYPE
2463: ,p_weighting_level_id
2464: in per_competence_elements.weighting_level_id%TYPE
2465: ,p_assessment_id
2466: in per_competence_elements.assessment_id%TYPE
2467: ,p_type
2468: in per_competence_elements.type%TYPE
2462: in per_competence_elements.rating_level_id%TYPE
2463: ,p_weighting_level_id
2464: in per_competence_elements.weighting_level_id%TYPE
2465: ,p_assessment_id
2466: in per_competence_elements.assessment_id%TYPE
2467: ,p_type
2468: in per_competence_elements.type%TYPE
2469: ,p_party_id
2470: in per_competence_elements.party_id%TYPE
2464: in per_competence_elements.weighting_level_id%TYPE
2465: ,p_assessment_id
2466: in per_competence_elements.assessment_id%TYPE
2467: ,p_type
2468: in per_competence_elements.type%TYPE
2469: ,p_party_id
2470: in per_competence_elements.party_id%TYPE
2471: ) is
2472: --
2466: in per_competence_elements.assessment_id%TYPE
2467: ,p_type
2468: in per_competence_elements.type%TYPE
2469: ,p_party_id
2470: in per_competence_elements.party_id%TYPE
2471: ) is
2472: --
2473: l_proc varchar2(72):= g_package||'chk_rating_weighting_id';
2474: l_api_updating boolean;
2512: );
2513: end if;
2514: --
2515: if hr_multi_message.no_exclusive_error
2516: ( p_check_column1 => 'PER_COMPETENCE_ELEMENTS.TYPE'
2517: ) then
2518: --
2519: -- Only proceed with validation if :
2520: -- a) The current g_old_rec is current and
2544: hr_utility.set_location(l_proc,10);
2545: hr_utility.set_message(801,'HR_51645_CEL_ASS_ASS_TP_NULL');
2546: hr_multi_message.add
2547: (p_associated_column1 =>
2548: 'PER_COMPETENCE_ELEMENTS.ASSESSMENT_ID'
2549: ,p_associated_column2 =>
2550: 'PER_COMPETENCE_ELEMENTS.RATING_LEVEL_ID'
2551: ,p_associated_column3 =>
2552: 'PER_COMPETENCE_ELEMENTS.WEIGHTING_LEVEL_ID'
2546: hr_multi_message.add
2547: (p_associated_column1 =>
2548: 'PER_COMPETENCE_ELEMENTS.ASSESSMENT_ID'
2549: ,p_associated_column2 =>
2550: 'PER_COMPETENCE_ELEMENTS.RATING_LEVEL_ID'
2551: ,p_associated_column3 =>
2552: 'PER_COMPETENCE_ELEMENTS.WEIGHTING_LEVEL_ID'
2553: ,p_associated_column4 =>
2554: 'PER_COMPETENCE_ELEMENTS.TYPE'
2548: 'PER_COMPETENCE_ELEMENTS.ASSESSMENT_ID'
2549: ,p_associated_column2 =>
2550: 'PER_COMPETENCE_ELEMENTS.RATING_LEVEL_ID'
2551: ,p_associated_column3 =>
2552: 'PER_COMPETENCE_ELEMENTS.WEIGHTING_LEVEL_ID'
2553: ,p_associated_column4 =>
2554: 'PER_COMPETENCE_ELEMENTS.TYPE'
2555: );
2556:
2550: 'PER_COMPETENCE_ELEMENTS.RATING_LEVEL_ID'
2551: ,p_associated_column3 =>
2552: 'PER_COMPETENCE_ELEMENTS.WEIGHTING_LEVEL_ID'
2553: ,p_associated_column4 =>
2554: 'PER_COMPETENCE_ELEMENTS.TYPE'
2555: );
2556:
2557: end if;
2558: --
2578: if p_rating_level_id is not null then
2579: --
2580: hr_multi_message.add
2581: (p_associated_column1 =>
2582: 'PER_COMPETENCE_ELEMENTS.ASSESSMENT_ID'
2583: ,p_associated_column2 =>
2584: 'PER_COMPETENCE_ELEMENTS.RATING_LEVEL_ID'
2585: ,p_associated_column3 =>
2586: 'PER_COMPETENCE_ELEMENTS.TYPE'
2580: hr_multi_message.add
2581: (p_associated_column1 =>
2582: 'PER_COMPETENCE_ELEMENTS.ASSESSMENT_ID'
2583: ,p_associated_column2 =>
2584: 'PER_COMPETENCE_ELEMENTS.RATING_LEVEL_ID'
2585: ,p_associated_column3 =>
2586: 'PER_COMPETENCE_ELEMENTS.TYPE'
2587: );
2588: else
2582: 'PER_COMPETENCE_ELEMENTS.ASSESSMENT_ID'
2583: ,p_associated_column2 =>
2584: 'PER_COMPETENCE_ELEMENTS.RATING_LEVEL_ID'
2585: ,p_associated_column3 =>
2586: 'PER_COMPETENCE_ELEMENTS.TYPE'
2587: );
2588: else
2589: --
2590: hr_multi_message.add
2588: else
2589: --
2590: hr_multi_message.add
2591: (p_associated_column1 =>
2592: 'PER_COMPETENCE_ELEMENTS.ASSESSMENT_ID'
2593: ,p_associated_column2 =>
2594: 'PER_COMPETENCE_ELEMENTS.WEIGHTING_LEVEL_ID'
2595: ,p_associated_column3 =>
2596: 'PER_COMPETENCE_ELEMENTS.TYPE'
2590: hr_multi_message.add
2591: (p_associated_column1 =>
2592: 'PER_COMPETENCE_ELEMENTS.ASSESSMENT_ID'
2593: ,p_associated_column2 =>
2594: 'PER_COMPETENCE_ELEMENTS.WEIGHTING_LEVEL_ID'
2595: ,p_associated_column3 =>
2596: 'PER_COMPETENCE_ELEMENTS.TYPE'
2597: );
2598: end if;
2592: 'PER_COMPETENCE_ELEMENTS.ASSESSMENT_ID'
2593: ,p_associated_column2 =>
2594: 'PER_COMPETENCE_ELEMENTS.WEIGHTING_LEVEL_ID'
2595: ,p_associated_column3 =>
2596: 'PER_COMPETENCE_ELEMENTS.TYPE'
2597: );
2598: end if;
2599: --
2600: else
2653: --
2654: --
2655: procedure chk_competence_element_dates
2656: (p_competence_element_id
2657: in per_competence_elements.competence_element_id%TYPE
2658: ,p_business_group_id
2659: in per_competence_elements.business_group_id%TYPE
2660: ,p_competence_id
2661: in per_competence_elements.competence_id%TYPE
2655: procedure chk_competence_element_dates
2656: (p_competence_element_id
2657: in per_competence_elements.competence_element_id%TYPE
2658: ,p_business_group_id
2659: in per_competence_elements.business_group_id%TYPE
2660: ,p_competence_id
2661: in per_competence_elements.competence_id%TYPE
2662: ,p_object_version_number
2663: in per_competence_elements.object_version_number%TYPE
2657: in per_competence_elements.competence_element_id%TYPE
2658: ,p_business_group_id
2659: in per_competence_elements.business_group_id%TYPE
2660: ,p_competence_id
2661: in per_competence_elements.competence_id%TYPE
2662: ,p_object_version_number
2663: in per_competence_elements.object_version_number%TYPE
2664: ,p_person_id
2665: in per_competence_elements.person_id%TYPE
2659: in per_competence_elements.business_group_id%TYPE
2660: ,p_competence_id
2661: in per_competence_elements.competence_id%TYPE
2662: ,p_object_version_number
2663: in per_competence_elements.object_version_number%TYPE
2664: ,p_person_id
2665: in per_competence_elements.person_id%TYPE
2666: ,p_position_id
2667: in per_competence_elements.position_id%TYPE
2661: in per_competence_elements.competence_id%TYPE
2662: ,p_object_version_number
2663: in per_competence_elements.object_version_number%TYPE
2664: ,p_person_id
2665: in per_competence_elements.person_id%TYPE
2666: ,p_position_id
2667: in per_competence_elements.position_id%TYPE
2668: ,p_organization_id
2669: in per_competence_elements.organization_id%TYPE
2663: in per_competence_elements.object_version_number%TYPE
2664: ,p_person_id
2665: in per_competence_elements.person_id%TYPE
2666: ,p_position_id
2667: in per_competence_elements.position_id%TYPE
2668: ,p_organization_id
2669: in per_competence_elements.organization_id%TYPE
2670: ,p_job_id
2671: in per_competence_elements.job_id%TYPE
2665: in per_competence_elements.person_id%TYPE
2666: ,p_position_id
2667: in per_competence_elements.position_id%TYPE
2668: ,p_organization_id
2669: in per_competence_elements.organization_id%TYPE
2670: ,p_job_id
2671: in per_competence_elements.job_id%TYPE
2672: ,p_valid_grade_id
2673: in per_competence_elements.valid_grade_id%TYPE
2667: in per_competence_elements.position_id%TYPE
2668: ,p_organization_id
2669: in per_competence_elements.organization_id%TYPE
2670: ,p_job_id
2671: in per_competence_elements.job_id%TYPE
2672: ,p_valid_grade_id
2673: in per_competence_elements.valid_grade_id%TYPE
2674: ,p_effective_date_from
2675: in per_competence_elements.effective_date_from%type
2669: in per_competence_elements.organization_id%TYPE
2670: ,p_job_id
2671: in per_competence_elements.job_id%TYPE
2672: ,p_valid_grade_id
2673: in per_competence_elements.valid_grade_id%TYPE
2674: ,p_effective_date_from
2675: in per_competence_elements.effective_date_from%type
2676: ,p_effective_date_to
2677: in per_competence_elements.effective_date_to%TYPE
2671: in per_competence_elements.job_id%TYPE
2672: ,p_valid_grade_id
2673: in per_competence_elements.valid_grade_id%TYPE
2674: ,p_effective_date_from
2675: in per_competence_elements.effective_date_from%type
2676: ,p_effective_date_to
2677: in per_competence_elements.effective_date_to%TYPE
2678: ,p_enterprise_id
2679: in per_competence_elements.enterprise_id%TYPE
2673: in per_competence_elements.valid_grade_id%TYPE
2674: ,p_effective_date_from
2675: in per_competence_elements.effective_date_from%type
2676: ,p_effective_date_to
2677: in per_competence_elements.effective_date_to%TYPE
2678: ,p_enterprise_id
2679: in per_competence_elements.enterprise_id%TYPE
2680: ) is
2681: --
2675: in per_competence_elements.effective_date_from%type
2676: ,p_effective_date_to
2677: in per_competence_elements.effective_date_to%TYPE
2678: ,p_enterprise_id
2679: in per_competence_elements.enterprise_id%TYPE
2680: ) is
2681: --
2682: l_proc varchar2(72)
2683: := g_package||'chk_competence_element_dates';
2707: else
2708: v_check_type := p_check_type || '_ID';
2709: end if;
2710:
2711: open csr_check_dates for 'select null from per_competence_elements where '
2712: || v_check_type || ' = :p_key_id'
2713: || ' and competence_id = :p_competence_id'
2714: || ' and nvl(business_group_id,-999) = nvl(:p_business_group_id,-999)'
2715: || ' and :p_effective_date_from <= nvl(effective_date_to,:eot1)'
2725: hr_utility.set_location(l_proc,2);
2726: close csr_check_dates;
2727: hr_utility.set_message(p_error_app,p_error);
2728: if p_check_type = 'ENTERPRISE' then
2729: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.ENTERPRISE_ID';
2730: elsif p_check_type = 'PERSON' then
2731: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.PERSON_ID';
2732: elsif p_check_type = 'JOB' then
2733: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.JOB_ID';
2727: hr_utility.set_message(p_error_app,p_error);
2728: if p_check_type = 'ENTERPRISE' then
2729: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.ENTERPRISE_ID';
2730: elsif p_check_type = 'PERSON' then
2731: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.PERSON_ID';
2732: elsif p_check_type = 'JOB' then
2733: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.JOB_ID';
2734: elsif p_check_type = 'POSITION' then
2735: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.POSITION_ID';
2729: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.ENTERPRISE_ID';
2730: elsif p_check_type = 'PERSON' then
2731: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.PERSON_ID';
2732: elsif p_check_type = 'JOB' then
2733: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.JOB_ID';
2734: elsif p_check_type = 'POSITION' then
2735: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.POSITION_ID';
2736: elsif p_check_type = 'GRADE' then
2737: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID';
2731: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.PERSON_ID';
2732: elsif p_check_type = 'JOB' then
2733: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.JOB_ID';
2734: elsif p_check_type = 'POSITION' then
2735: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.POSITION_ID';
2736: elsif p_check_type = 'GRADE' then
2737: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID';
2738: elsif p_check_type = 'ORGANIZATION' then
2739: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.ORGANIZATION_ID';
2733: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.JOB_ID';
2734: elsif p_check_type = 'POSITION' then
2735: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.POSITION_ID';
2736: elsif p_check_type = 'GRADE' then
2737: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID';
2738: elsif p_check_type = 'ORGANIZATION' then
2739: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.ORGANIZATION_ID';
2740: end if;
2741: if p_valid_grade_null is null then
2735: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.POSITION_ID';
2736: elsif p_check_type = 'GRADE' then
2737: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID';
2738: elsif p_check_type = 'ORGANIZATION' then
2739: l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.ORGANIZATION_ID';
2740: end if;
2741: if p_valid_grade_null is null then
2742: hr_multi_message.add
2743: (
2741: if p_valid_grade_null is null then
2742: hr_multi_message.add
2743: (
2744: p_associated_column1 => l_associate_attribute
2745: -- p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2746: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2747: ,p_associated_column3 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2748: ,p_associated_column4 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
2749: );
2742: hr_multi_message.add
2743: (
2744: p_associated_column1 => l_associate_attribute
2745: -- p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2746: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2747: ,p_associated_column3 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2748: ,p_associated_column4 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
2749: );
2750: else
2743: (
2744: p_associated_column1 => l_associate_attribute
2745: -- p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2746: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2747: ,p_associated_column3 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2748: ,p_associated_column4 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
2749: );
2750: else
2751: hr_multi_message.add
2744: p_associated_column1 => l_associate_attribute
2745: -- p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2746: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2747: ,p_associated_column3 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2748: ,p_associated_column4 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
2749: );
2750: else
2751: hr_multi_message.add
2752: (
2750: else
2751: hr_multi_message.add
2752: (
2753: p_associated_column1 => l_associate_attribute
2754: -- p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2755: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2756: ,p_associated_column3 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2757: ,p_associated_column4 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
2758: ,p_associated_column5 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID'
2751: hr_multi_message.add
2752: (
2753: p_associated_column1 => l_associate_attribute
2754: -- p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2755: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2756: ,p_associated_column3 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2757: ,p_associated_column4 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
2758: ,p_associated_column5 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID'
2759: );
2752: (
2753: p_associated_column1 => l_associate_attribute
2754: -- p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2755: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2756: ,p_associated_column3 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2757: ,p_associated_column4 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
2758: ,p_associated_column5 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID'
2759: );
2760: end if;
2753: p_associated_column1 => l_associate_attribute
2754: -- p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2755: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2756: ,p_associated_column3 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2757: ,p_associated_column4 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
2758: ,p_associated_column5 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID'
2759: );
2760: end if;
2761: else -- if no records are found in the cursor, close the cursor.
2754: -- p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2755: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2756: ,p_associated_column3 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2757: ,p_associated_column4 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
2758: ,p_associated_column5 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID'
2759: );
2760: end if;
2761: else -- if no records are found in the cursor, close the cursor.
2762: hr_utility.set_location(l_proc,3);
2804: hr_utility.set_location(l_proc,10);
2805: hr_utility.set_message(801,'HR_51647_CEL_DATES_INVL');
2806: --
2807: hr_multi_message.add
2808: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM');
2809:
2810: elsif (p_enterprise_id is not null) then
2811: --
2812: -- Check that the dates for enterprise_id does not overlap.
2912: --
2913: --
2914: procedure chk_normal_elapse_duration
2915: (p_competence_element_id
2916: in per_competence_elements.competence_element_id%TYPE
2917: ,p_object_version_number
2918: in per_competence_elements.object_version_number%TYPE
2919: ,p_effective_date
2920: in Date
2914: procedure chk_normal_elapse_duration
2915: (p_competence_element_id
2916: in per_competence_elements.competence_element_id%TYPE
2917: ,p_object_version_number
2918: in per_competence_elements.object_version_number%TYPE
2919: ,p_effective_date
2920: in Date
2921: ,p_normal_elapse_duration
2922: in per_competence_elements.normal_elapse_duration%TYPE
2918: in per_competence_elements.object_version_number%TYPE
2919: ,p_effective_date
2920: in Date
2921: ,p_normal_elapse_duration
2922: in per_competence_elements.normal_elapse_duration%TYPE
2923: ,p_normal_elapse_duration_unit
2924: in per_competence_elements.normal_elapse_duration_unit%TYPE
2925: ) is
2926: --
2920: in Date
2921: ,p_normal_elapse_duration
2922: in per_competence_elements.normal_elapse_duration%TYPE
2923: ,p_normal_elapse_duration_unit
2924: in per_competence_elements.normal_elapse_duration_unit%TYPE
2925: ) is
2926: --
2927: l_proc varchar2(72)
2928: := g_package||'chk_normal_elapse_duration';
2975: hr_utility.set_location(l_proc,10);
2976:
2977: hr_utility.set_message(801,'HR_51653_CEL_NOR_ELPS_COMB');
2978: hr_multi_message.add
2979: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.NORMAL_ELAPSE_DURATION'
2980: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.NORMAL_ELAPSE_DURATION_UNIT'
2981: );
2982: elsif (p_normal_elapse_duration IS NOT NULL AND
2983: p_normal_elapse_duration_unit IS NOT NULL) then
2976:
2977: hr_utility.set_message(801,'HR_51653_CEL_NOR_ELPS_COMB');
2978: hr_multi_message.add
2979: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.NORMAL_ELAPSE_DURATION'
2980: ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.NORMAL_ELAPSE_DURATION_UNIT'
2981: );
2982: elsif (p_normal_elapse_duration IS NOT NULL AND
2983: p_normal_elapse_duration_unit IS NOT NULL) then
2984: --
2994: -- Error: Invalid normal_elapse_duration_unit
2995: hr_utility.set_location(l_proc, 15);
2996: hr_utility.set_message(801,'HR_51654_CEL_NOR_ELPS_INVL');
2997: hr_multi_message.add
2998: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.NORMAL_ELAPSE_DURATION_UNIT'
2999: );
3000: end if;
3001: end if;
3002: end if;
3057: --
3058: --
3059: procedure chk_rating_weighting_ass_type
3060: (p_competence_element_id
3061: in per_competence_elements.competence_element_id%TYPE
3062: ,p_business_group_id
3063: in per_competence_elements.business_group_id%TYPE
3064: ,p_object_version_number
3065:
3059: procedure chk_rating_weighting_ass_type
3060: (p_competence_element_id
3061: in per_competence_elements.competence_element_id%TYPE
3062: ,p_business_group_id
3063: in per_competence_elements.business_group_id%TYPE
3064: ,p_object_version_number
3065:
3066: in per_competence_elements.object_version_number%TYPE
3067: ,p_rating_level_id
3062: ,p_business_group_id
3063: in per_competence_elements.business_group_id%TYPE
3064: ,p_object_version_number
3065:
3066: in per_competence_elements.object_version_number%TYPE
3067: ,p_rating_level_id
3068: in per_competence_elements.rating_level_id%TYPE
3069: ,p_weighting_level_id
3070: in per_competence_elements.weighting_level_id%TYPE
3064: ,p_object_version_number
3065:
3066: in per_competence_elements.object_version_number%TYPE
3067: ,p_rating_level_id
3068: in per_competence_elements.rating_level_id%TYPE
3069: ,p_weighting_level_id
3070: in per_competence_elements.weighting_level_id%TYPE
3071: ,p_assessment_id
3072: in per_competence_elements.assessment_id%TYPE
3066: in per_competence_elements.object_version_number%TYPE
3067: ,p_rating_level_id
3068: in per_competence_elements.rating_level_id%TYPE
3069: ,p_weighting_level_id
3070: in per_competence_elements.weighting_level_id%TYPE
3071: ,p_assessment_id
3072: in per_competence_elements.assessment_id%TYPE
3073: ,p_assessment_type_id
3074: in per_competence_elements.assessment_type_id%TYPE
3068: in per_competence_elements.rating_level_id%TYPE
3069: ,p_weighting_level_id
3070: in per_competence_elements.weighting_level_id%TYPE
3071: ,p_assessment_id
3072: in per_competence_elements.assessment_id%TYPE
3073: ,p_assessment_type_id
3074: in per_competence_elements.assessment_type_id%TYPE
3075: ) is
3076: --
3070: in per_competence_elements.weighting_level_id%TYPE
3071: ,p_assessment_id
3072: in per_competence_elements.assessment_id%TYPE
3073: ,p_assessment_type_id
3074: in per_competence_elements.assessment_type_id%TYPE
3075: ) is
3076: --
3077: l_proc varchar2(72)
3078: := g_package||'chk_rating_weighting_ass_type';
3274: --
3275: --
3276: procedure chk_type_and_validation
3277: (p_competence_element_id
3278: in per_competence_elements.competence_element_ID%TYPE
3279: ,p_object_version_number
3280: in per_competence_elements.object_version_number%TYPE
3281: ,p_business_group_id
3282: in per_competence_elements.business_group_id%TYPE
3276: procedure chk_type_and_validation
3277: (p_competence_element_id
3278: in per_competence_elements.competence_element_ID%TYPE
3279: ,p_object_version_number
3280: in per_competence_elements.object_version_number%TYPE
3281: ,p_business_group_id
3282: in per_competence_elements.business_group_id%TYPE
3283: ,p_enterprise_id
3284: in per_competence_elements.enterprise_id%TYPE
3278: in per_competence_elements.competence_element_ID%TYPE
3279: ,p_object_version_number
3280: in per_competence_elements.object_version_number%TYPE
3281: ,p_business_group_id
3282: in per_competence_elements.business_group_id%TYPE
3283: ,p_enterprise_id
3284: in per_competence_elements.enterprise_id%TYPE
3285: ,p_type
3286: in per_competence_elements.type%TYPE
3280: in per_competence_elements.object_version_number%TYPE
3281: ,p_business_group_id
3282: in per_competence_elements.business_group_id%TYPE
3283: ,p_enterprise_id
3284: in per_competence_elements.enterprise_id%TYPE
3285: ,p_type
3286: in per_competence_elements.type%TYPE
3287: ,p_competence_id
3288: in per_competence_elements.competence_id%TYPE
3282: in per_competence_elements.business_group_id%TYPE
3283: ,p_enterprise_id
3284: in per_competence_elements.enterprise_id%TYPE
3285: ,p_type
3286: in per_competence_elements.type%TYPE
3287: ,p_competence_id
3288: in per_competence_elements.competence_id%TYPE
3289: ,p_assessment_id
3290: in per_competence_elements.assessment_id%TYPE
3284: in per_competence_elements.enterprise_id%TYPE
3285: ,p_type
3286: in per_competence_elements.type%TYPE
3287: ,p_competence_id
3288: in per_competence_elements.competence_id%TYPE
3289: ,p_assessment_id
3290: in per_competence_elements.assessment_id%TYPE
3291: ,p_assessment_type_id
3292: in per_competence_elements.assessment_type_id%TYPE
3286: in per_competence_elements.type%TYPE
3287: ,p_competence_id
3288: in per_competence_elements.competence_id%TYPE
3289: ,p_assessment_id
3290: in per_competence_elements.assessment_id%TYPE
3291: ,p_assessment_type_id
3292: in per_competence_elements.assessment_type_id%TYPE
3293: ,p_activity_version_id
3294: in per_competence_elements.activity_version_id%TYPE
3288: in per_competence_elements.competence_id%TYPE
3289: ,p_assessment_id
3290: in per_competence_elements.assessment_id%TYPE
3291: ,p_assessment_type_id
3292: in per_competence_elements.assessment_type_id%TYPE
3293: ,p_activity_version_id
3294: in per_competence_elements.activity_version_id%TYPE
3295: ,p_organization_id
3296: in per_competence_elements.organization_id%TYPE
3290: in per_competence_elements.assessment_id%TYPE
3291: ,p_assessment_type_id
3292: in per_competence_elements.assessment_type_id%TYPE
3293: ,p_activity_version_id
3294: in per_competence_elements.activity_version_id%TYPE
3295: ,p_organization_id
3296: in per_competence_elements.organization_id%TYPE
3297: ,p_job_id
3298: in per_competence_elements.job_id%TYPE
3292: in per_competence_elements.assessment_type_id%TYPE
3293: ,p_activity_version_id
3294: in per_competence_elements.activity_version_id%TYPE
3295: ,p_organization_id
3296: in per_competence_elements.organization_id%TYPE
3297: ,p_job_id
3298: in per_competence_elements.job_id%TYPE
3299: ,p_valid_grade_id
3300: in per_competence_elements.valid_grade_id%TYPE
3294: in per_competence_elements.activity_version_id%TYPE
3295: ,p_organization_id
3296: in per_competence_elements.organization_id%TYPE
3297: ,p_job_id
3298: in per_competence_elements.job_id%TYPE
3299: ,p_valid_grade_id
3300: in per_competence_elements.valid_grade_id%TYPE
3301: ,p_position_id
3302: in per_competence_elements.position_id%TYPE
3296: in per_competence_elements.organization_id%TYPE
3297: ,p_job_id
3298: in per_competence_elements.job_id%TYPE
3299: ,p_valid_grade_id
3300: in per_competence_elements.valid_grade_id%TYPE
3301: ,p_position_id
3302: in per_competence_elements.position_id%TYPE
3303: ,p_person_id
3304: in per_competence_elements.person_id%TYPE
3298: in per_competence_elements.job_id%TYPE
3299: ,p_valid_grade_id
3300: in per_competence_elements.valid_grade_id%TYPE
3301: ,p_position_id
3302: in per_competence_elements.position_id%TYPE
3303: ,p_person_id
3304: in per_competence_elements.person_id%TYPE
3305: ,p_parent_competence_element_id
3306: in per_competence_elements.parent_competence_element_id%TYPE
3300: in per_competence_elements.valid_grade_id%TYPE
3301: ,p_position_id
3302: in per_competence_elements.position_id%TYPE
3303: ,p_person_id
3304: in per_competence_elements.person_id%TYPE
3305: ,p_parent_competence_element_id
3306: in per_competence_elements.parent_competence_element_id%TYPE
3307: ,p_group_competence_type
3308: in per_competence_elements.group_competence_type%TYPE
3302: in per_competence_elements.position_id%TYPE
3303: ,p_person_id
3304: in per_competence_elements.person_id%TYPE
3305: ,p_parent_competence_element_id
3306: in per_competence_elements.parent_competence_element_id%TYPE
3307: ,p_group_competence_type
3308: in per_competence_elements.group_competence_type%TYPE
3309: ,p_effective_date_to
3310: in per_competence_elements.effective_date_to%TYPE
3304: in per_competence_elements.person_id%TYPE
3305: ,p_parent_competence_element_id
3306: in per_competence_elements.parent_competence_element_id%TYPE
3307: ,p_group_competence_type
3308: in per_competence_elements.group_competence_type%TYPE
3309: ,p_effective_date_to
3310: in per_competence_elements.effective_date_to%TYPE
3311: ,p_effective_date_from
3312: in per_competence_elements.effective_date_from%TYPE
3306: in per_competence_elements.parent_competence_element_id%TYPE
3307: ,p_group_competence_type
3308: in per_competence_elements.group_competence_type%TYPE
3309: ,p_effective_date_to
3310: in per_competence_elements.effective_date_to%TYPE
3311: ,p_effective_date_from
3312: in per_competence_elements.effective_date_from%TYPE
3313: ,p_proficiency_level_id
3314: in per_competence_elements.proficiency_level_id%TYPE
3308: in per_competence_elements.group_competence_type%TYPE
3309: ,p_effective_date_to
3310: in per_competence_elements.effective_date_to%TYPE
3311: ,p_effective_date_from
3312: in per_competence_elements.effective_date_from%TYPE
3313: ,p_proficiency_level_id
3314: in per_competence_elements.proficiency_level_id%TYPE
3315: ,p_certification_date
3316: in per_competence_elements.certification_date%TYPE
3310: in per_competence_elements.effective_date_to%TYPE
3311: ,p_effective_date_from
3312: in per_competence_elements.effective_date_from%TYPE
3313: ,p_proficiency_level_id
3314: in per_competence_elements.proficiency_level_id%TYPE
3315: ,p_certification_date
3316: in per_competence_elements.certification_date%TYPE
3317: ,p_certification_method
3318: in per_competence_elements.certification_method%TYPE
3312: in per_competence_elements.effective_date_from%TYPE
3313: ,p_proficiency_level_id
3314: in per_competence_elements.proficiency_level_id%TYPE
3315: ,p_certification_date
3316: in per_competence_elements.certification_date%TYPE
3317: ,p_certification_method
3318: in per_competence_elements.certification_method%TYPE
3319: ,p_next_certification_date
3320: in per_competence_elements.next_certification_date%TYPE
3314: in per_competence_elements.proficiency_level_id%TYPE
3315: ,p_certification_date
3316: in per_competence_elements.certification_date%TYPE
3317: ,p_certification_method
3318: in per_competence_elements.certification_method%TYPE
3319: ,p_next_certification_date
3320: in per_competence_elements.next_certification_date%TYPE
3321: ,p_mandatory
3322: in per_competence_elements.mandatory%TYPE
3316: in per_competence_elements.certification_date%TYPE
3317: ,p_certification_method
3318: in per_competence_elements.certification_method%TYPE
3319: ,p_next_certification_date
3320: in per_competence_elements.next_certification_date%TYPE
3321: ,p_mandatory
3322: in per_competence_elements.mandatory%TYPE
3323: ,p_normal_elapse_duration
3324: in per_competence_elements.normal_elapse_duration%TYPE
3318: in per_competence_elements.certification_method%TYPE
3319: ,p_next_certification_date
3320: in per_competence_elements.next_certification_date%TYPE
3321: ,p_mandatory
3322: in per_competence_elements.mandatory%TYPE
3323: ,p_normal_elapse_duration
3324: in per_competence_elements.normal_elapse_duration%TYPE
3325: ,p_normal_elapse_duration_unit
3326: in per_competence_elements.normal_elapse_duration_unit%TYPE
3320: in per_competence_elements.next_certification_date%TYPE
3321: ,p_mandatory
3322: in per_competence_elements.mandatory%TYPE
3323: ,p_normal_elapse_duration
3324: in per_competence_elements.normal_elapse_duration%TYPE
3325: ,p_normal_elapse_duration_unit
3326: in per_competence_elements.normal_elapse_duration_unit%TYPE
3327: ,p_high_proficiency_level_id
3328: in per_competence_elements.high_proficiency_level_id%TYPE
3322: in per_competence_elements.mandatory%TYPE
3323: ,p_normal_elapse_duration
3324: in per_competence_elements.normal_elapse_duration%TYPE
3325: ,p_normal_elapse_duration_unit
3326: in per_competence_elements.normal_elapse_duration_unit%TYPE
3327: ,p_high_proficiency_level_id
3328: in per_competence_elements.high_proficiency_level_id%TYPE
3329: ,p_competence_type
3330: in per_competence_elements.competence_type%TYPE
3324: in per_competence_elements.normal_elapse_duration%TYPE
3325: ,p_normal_elapse_duration_unit
3326: in per_competence_elements.normal_elapse_duration_unit%TYPE
3327: ,p_high_proficiency_level_id
3328: in per_competence_elements.high_proficiency_level_id%TYPE
3329: ,p_competence_type
3330: in per_competence_elements.competence_type%TYPE
3331: ,p_sequence_number
3332: in per_competence_elements.sequence_number%TYPE
3326: in per_competence_elements.normal_elapse_duration_unit%TYPE
3327: ,p_high_proficiency_level_id
3328: in per_competence_elements.high_proficiency_level_id%TYPE
3329: ,p_competence_type
3330: in per_competence_elements.competence_type%TYPE
3331: ,p_sequence_number
3332: in per_competence_elements.sequence_number%TYPE
3333: ,p_source_of_proficiency_level
3334: in per_competence_elements.source_of_proficiency_level%TYPE
3328: in per_competence_elements.high_proficiency_level_id%TYPE
3329: ,p_competence_type
3330: in per_competence_elements.competence_type%TYPE
3331: ,p_sequence_number
3332: in per_competence_elements.sequence_number%TYPE
3333: ,p_source_of_proficiency_level
3334: in per_competence_elements.source_of_proficiency_level%TYPE
3335: ,p_weighting_level_id
3336: in per_competence_elements.weighting_level_id%TYPE
3330: in per_competence_elements.competence_type%TYPE
3331: ,p_sequence_number
3332: in per_competence_elements.sequence_number%TYPE
3333: ,p_source_of_proficiency_level
3334: in per_competence_elements.source_of_proficiency_level%TYPE
3335: ,p_weighting_level_id
3336: in per_competence_elements.weighting_level_id%TYPE
3337: ,p_rating_level_id
3338: in per_competence_elements.rating_level_id%TYPE
3332: in per_competence_elements.sequence_number%TYPE
3333: ,p_source_of_proficiency_level
3334: in per_competence_elements.source_of_proficiency_level%TYPE
3335: ,p_weighting_level_id
3336: in per_competence_elements.weighting_level_id%TYPE
3337: ,p_rating_level_id
3338: in per_competence_elements.rating_level_id%TYPE
3339: ,p_line_score
3340: in per_competence_elements.line_score%TYPE
3334: in per_competence_elements.source_of_proficiency_level%TYPE
3335: ,p_weighting_level_id
3336: in per_competence_elements.weighting_level_id%TYPE
3337: ,p_rating_level_id
3338: in per_competence_elements.rating_level_id%TYPE
3339: ,p_line_score
3340: in per_competence_elements.line_score%TYPE
3341: ,p_object_id
3342: in per_competence_elements.object_id%TYPE
3336: in per_competence_elements.weighting_level_id%TYPE
3337: ,p_rating_level_id
3338: in per_competence_elements.rating_level_id%TYPE
3339: ,p_line_score
3340: in per_competence_elements.line_score%TYPE
3341: ,p_object_id
3342: in per_competence_elements.object_id%TYPE
3343: ,p_object_name
3344: in per_competence_elements.object_name%TYPE
3338: in per_competence_elements.rating_level_id%TYPE
3339: ,p_line_score
3340: in per_competence_elements.line_score%TYPE
3341: ,p_object_id
3342: in per_competence_elements.object_id%TYPE
3343: ,p_object_name
3344: in per_competence_elements.object_name%TYPE
3345: ,p_party_id -- HR/TCA merge
3346: in per_competence_elements.party_id%TYPE
3340: in per_competence_elements.line_score%TYPE
3341: ,p_object_id
3342: in per_competence_elements.object_id%TYPE
3343: ,p_object_name
3344: in per_competence_elements.object_name%TYPE
3345: ,p_party_id -- HR/TCA merge
3346: in per_competence_elements.party_id%TYPE
3347: ,p_qualification_type_id -- BUG3356369
3348: in per_competence_elements.qualification_type_id%TYPE
3342: in per_competence_elements.object_id%TYPE
3343: ,p_object_name
3344: in per_competence_elements.object_name%TYPE
3345: ,p_party_id -- HR/TCA merge
3346: in per_competence_elements.party_id%TYPE
3347: ,p_qualification_type_id -- BUG3356369
3348: in per_competence_elements.qualification_type_id%TYPE
3349: ) is
3350: --
3344: in per_competence_elements.object_name%TYPE
3345: ,p_party_id -- HR/TCA merge
3346: in per_competence_elements.party_id%TYPE
3347: ,p_qualification_type_id -- BUG3356369
3348: in per_competence_elements.qualification_type_id%TYPE
3349: ) is
3350: --
3351: -- Cursor to check that the parent_competence_element has
3352: -- type 'ASSESSMENT_GROUP'
3352: -- type 'ASSESSMENT_GROUP'
3353: --
3354: cursor csr_parent_comp_element is
3355: select null
3356: from per_competence_elements
3357: where competence_element_id = p_parent_competence_element_id
3358: and nvl(business_group_id,-1) = nvl(p_business_group_id,-1)
3359: and type = 'ASSESSMENT_GROUP';
3360:
3362: -- Cursor to check that the combination of competence and
3363: -- qualification_type is unique when thpe is 'QUALIFICATION'
3364: --
3365: cursor csr_comp_qual_link is
3366: select null from per_competence_elements cel
3367: where cel.competence_id = p_competence_id
3368: and cel.qualification_type_id = p_qualification_type_id
3369: and cel.type = 'QUALIFICATION'
3370: and cel.qualification_type_id = p_qualification_type_id
3371: and (p_effective_date_from <= nvl(cel.effective_date_to, hr_api.g_eot)
3372: and NVL(p_effective_date_to, hr_api.g_eot) >= cel.effective_date_from);
3373:
3374: cursor csr_upd_comp_qual_link is
3375: select null from per_competence_elements cel
3376: where cel.competence_element_id <> p_competence_element_id
3377: and cel.competence_id = p_competence_id
3378: and cel.qualification_type_id = p_qualification_type_id
3379: and cel.type = 'QUALIFICATION'
3390: begin
3391: hr_utility.set_location('Entering:'|| l_proc, 1);
3392: --
3393: if hr_multi_message.no_exclusive_error
3394: (p_check_column1 => 'PER_COMPETENCE_ELEMENTS.TYPE') then
3395: --
3396: -- Check mandatory parameters have being set.
3397: --
3398: hr_api.mandatory_arg_error
3494: AND (p_valid_grade_id is not null) ) then
3495: hr_utility.set_location(l_proc, 8);
3496: hr_utility.set_message(800,'HR_52373_CEL_GRD_ID_MST_NULL');
3497: hr_multi_message.add
3498: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID');
3499:
3500: hr_utility.raise_error;
3501: --
3502: elsif ((p_enterprise_id is not null AND(p_organization_id
3864: --
3865: hr_utility.set_location(l_proc, 75);
3866: hr_utility.set_message(801,'HR_51670_CEL_PER_TYPE_ERROR');
3867: hr_multi_message.add
3868: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM');
3869: hr_utility.raise_error;
3870: --
3871: end if;
3872: --
3993: end if; -- check for no_exclusive_error for TYPE.
3994: exception
3995: when app_exception.application_exception then
3996: if hr_multi_message.exception_add
3997: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.TYPE'
3998: ) then
3999: hr_utility.set_location(' Leaving:'||l_proc,110);
4000: raise;
4001: end if;
4054: --
4055: --
4056: procedure chk_unique_competence_element
4057: (p_competence_element_id
4058: in per_competence_elements.competence_element_ID%TYPE
4059: ,p_object_version_number
4060: in per_competence_elements.object_version_number%TYPE
4061: ,p_business_group_id
4062: in per_competence_elements.business_group_id%TYPE
4056: procedure chk_unique_competence_element
4057: (p_competence_element_id
4058: in per_competence_elements.competence_element_ID%TYPE
4059: ,p_object_version_number
4060: in per_competence_elements.object_version_number%TYPE
4061: ,p_business_group_id
4062: in per_competence_elements.business_group_id%TYPE
4063: ,p_enterprise_id
4064: in per_competence_elements.enterprise_id%TYPE
4058: in per_competence_elements.competence_element_ID%TYPE
4059: ,p_object_version_number
4060: in per_competence_elements.object_version_number%TYPE
4061: ,p_business_group_id
4062: in per_competence_elements.business_group_id%TYPE
4063: ,p_enterprise_id
4064: in per_competence_elements.enterprise_id%TYPE
4065: ,p_type in per_competence_elements.type%TYPE
4066: ,p_competence_id in per_competence_elements.competence_id%TYPE
4060: in per_competence_elements.object_version_number%TYPE
4061: ,p_business_group_id
4062: in per_competence_elements.business_group_id%TYPE
4063: ,p_enterprise_id
4064: in per_competence_elements.enterprise_id%TYPE
4065: ,p_type in per_competence_elements.type%TYPE
4066: ,p_competence_id in per_competence_elements.competence_id%TYPE
4067: ,p_assessment_id in per_competence_elements.assessment_id%TYPE
4068: ,p_assessment_type_id
4061: ,p_business_group_id
4062: in per_competence_elements.business_group_id%TYPE
4063: ,p_enterprise_id
4064: in per_competence_elements.enterprise_id%TYPE
4065: ,p_type in per_competence_elements.type%TYPE
4066: ,p_competence_id in per_competence_elements.competence_id%TYPE
4067: ,p_assessment_id in per_competence_elements.assessment_id%TYPE
4068: ,p_assessment_type_id
4069: in per_competence_elements.assessment_type_id%TYPE
4062: in per_competence_elements.business_group_id%TYPE
4063: ,p_enterprise_id
4064: in per_competence_elements.enterprise_id%TYPE
4065: ,p_type in per_competence_elements.type%TYPE
4066: ,p_competence_id in per_competence_elements.competence_id%TYPE
4067: ,p_assessment_id in per_competence_elements.assessment_id%TYPE
4068: ,p_assessment_type_id
4069: in per_competence_elements.assessment_type_id%TYPE
4070: ,p_activity_version_id
4063: ,p_enterprise_id
4064: in per_competence_elements.enterprise_id%TYPE
4065: ,p_type in per_competence_elements.type%TYPE
4066: ,p_competence_id in per_competence_elements.competence_id%TYPE
4067: ,p_assessment_id in per_competence_elements.assessment_id%TYPE
4068: ,p_assessment_type_id
4069: in per_competence_elements.assessment_type_id%TYPE
4070: ,p_activity_version_id
4071: in per_competence_elements.activity_version_id%TYPE
4065: ,p_type in per_competence_elements.type%TYPE
4066: ,p_competence_id in per_competence_elements.competence_id%TYPE
4067: ,p_assessment_id in per_competence_elements.assessment_id%TYPE
4068: ,p_assessment_type_id
4069: in per_competence_elements.assessment_type_id%TYPE
4070: ,p_activity_version_id
4071: in per_competence_elements.activity_version_id%TYPE
4072: ,p_organization_id
4073: in per_competence_elements.organization_id%TYPE
4067: ,p_assessment_id in per_competence_elements.assessment_id%TYPE
4068: ,p_assessment_type_id
4069: in per_competence_elements.assessment_type_id%TYPE
4070: ,p_activity_version_id
4071: in per_competence_elements.activity_version_id%TYPE
4072: ,p_organization_id
4073: in per_competence_elements.organization_id%TYPE
4074: ,p_job_id in per_competence_elements.job_id%TYPE
4075: ,p_valid_grade_id in per_competence_elements.valid_grade_id%TYPE
4069: in per_competence_elements.assessment_type_id%TYPE
4070: ,p_activity_version_id
4071: in per_competence_elements.activity_version_id%TYPE
4072: ,p_organization_id
4073: in per_competence_elements.organization_id%TYPE
4074: ,p_job_id in per_competence_elements.job_id%TYPE
4075: ,p_valid_grade_id in per_competence_elements.valid_grade_id%TYPE
4076: ,p_position_id
4077: in per_competence_elements.position_id%TYPE
4070: ,p_activity_version_id
4071: in per_competence_elements.activity_version_id%TYPE
4072: ,p_organization_id
4073: in per_competence_elements.organization_id%TYPE
4074: ,p_job_id in per_competence_elements.job_id%TYPE
4075: ,p_valid_grade_id in per_competence_elements.valid_grade_id%TYPE
4076: ,p_position_id
4077: in per_competence_elements.position_id%TYPE
4078: ,p_person_id in per_competence_elements.person_id%TYPE
4071: in per_competence_elements.activity_version_id%TYPE
4072: ,p_organization_id
4073: in per_competence_elements.organization_id%TYPE
4074: ,p_job_id in per_competence_elements.job_id%TYPE
4075: ,p_valid_grade_id in per_competence_elements.valid_grade_id%TYPE
4076: ,p_position_id
4077: in per_competence_elements.position_id%TYPE
4078: ,p_person_id in per_competence_elements.person_id%TYPE
4079: ,p_parent_competence_element_id
4073: in per_competence_elements.organization_id%TYPE
4074: ,p_job_id in per_competence_elements.job_id%TYPE
4075: ,p_valid_grade_id in per_competence_elements.valid_grade_id%TYPE
4076: ,p_position_id
4077: in per_competence_elements.position_id%TYPE
4078: ,p_person_id in per_competence_elements.person_id%TYPE
4079: ,p_parent_competence_element_id
4080: in per_competence_elements.parent_competence_element_id%TYPE
4081: ,p_group_competence_type
4074: ,p_job_id in per_competence_elements.job_id%TYPE
4075: ,p_valid_grade_id in per_competence_elements.valid_grade_id%TYPE
4076: ,p_position_id
4077: in per_competence_elements.position_id%TYPE
4078: ,p_person_id in per_competence_elements.person_id%TYPE
4079: ,p_parent_competence_element_id
4080: in per_competence_elements.parent_competence_element_id%TYPE
4081: ,p_group_competence_type
4082: in per_competence_elements.group_competence_type%TYPE
4076: ,p_position_id
4077: in per_competence_elements.position_id%TYPE
4078: ,p_person_id in per_competence_elements.person_id%TYPE
4079: ,p_parent_competence_element_id
4080: in per_competence_elements.parent_competence_element_id%TYPE
4081: ,p_group_competence_type
4082: in per_competence_elements.group_competence_type%TYPE
4083: ,p_effective_date_from in per_competence_elements.effective_date_from%TYPE
4084: ,p_competence_type in per_competence_elements.competence_type%TYPE
4078: ,p_person_id in per_competence_elements.person_id%TYPE
4079: ,p_parent_competence_element_id
4080: in per_competence_elements.parent_competence_element_id%TYPE
4081: ,p_group_competence_type
4082: in per_competence_elements.group_competence_type%TYPE
4083: ,p_effective_date_from in per_competence_elements.effective_date_from%TYPE
4084: ,p_competence_type in per_competence_elements.competence_type%TYPE
4085: ,p_object_name in per_competence_elements.object_name%type
4086: ,p_object_id in per_competence_elements.object_id%type
4079: ,p_parent_competence_element_id
4080: in per_competence_elements.parent_competence_element_id%TYPE
4081: ,p_group_competence_type
4082: in per_competence_elements.group_competence_type%TYPE
4083: ,p_effective_date_from in per_competence_elements.effective_date_from%TYPE
4084: ,p_competence_type in per_competence_elements.competence_type%TYPE
4085: ,p_object_name in per_competence_elements.object_name%type
4086: ,p_object_id in per_competence_elements.object_id%type
4087: ,p_party_id in per_competence_elements.party_id%type -- HR/TCA merge
4080: in per_competence_elements.parent_competence_element_id%TYPE
4081: ,p_group_competence_type
4082: in per_competence_elements.group_competence_type%TYPE
4083: ,p_effective_date_from in per_competence_elements.effective_date_from%TYPE
4084: ,p_competence_type in per_competence_elements.competence_type%TYPE
4085: ,p_object_name in per_competence_elements.object_name%type
4086: ,p_object_id in per_competence_elements.object_id%type
4087: ,p_party_id in per_competence_elements.party_id%type -- HR/TCA merge
4088: ,p_qualification_type_id in per_competence_elements.qualification_type_id%type
4081: ,p_group_competence_type
4082: in per_competence_elements.group_competence_type%TYPE
4083: ,p_effective_date_from in per_competence_elements.effective_date_from%TYPE
4084: ,p_competence_type in per_competence_elements.competence_type%TYPE
4085: ,p_object_name in per_competence_elements.object_name%type
4086: ,p_object_id in per_competence_elements.object_id%type
4087: ,p_party_id in per_competence_elements.party_id%type -- HR/TCA merge
4088: ,p_qualification_type_id in per_competence_elements.qualification_type_id%type
4089: ) is
4082: in per_competence_elements.group_competence_type%TYPE
4083: ,p_effective_date_from in per_competence_elements.effective_date_from%TYPE
4084: ,p_competence_type in per_competence_elements.competence_type%TYPE
4085: ,p_object_name in per_competence_elements.object_name%type
4086: ,p_object_id in per_competence_elements.object_id%type
4087: ,p_party_id in per_competence_elements.party_id%type -- HR/TCA merge
4088: ,p_qualification_type_id in per_competence_elements.qualification_type_id%type
4089: ) is
4090: --
4083: ,p_effective_date_from in per_competence_elements.effective_date_from%TYPE
4084: ,p_competence_type in per_competence_elements.competence_type%TYPE
4085: ,p_object_name in per_competence_elements.object_name%type
4086: ,p_object_id in per_competence_elements.object_id%type
4087: ,p_party_id in per_competence_elements.party_id%type -- HR/TCA merge
4088: ,p_qualification_type_id in per_competence_elements.qualification_type_id%type
4089: ) is
4090: --
4091: l_proc varchar2(72):=
4084: ,p_competence_type in per_competence_elements.competence_type%TYPE
4085: ,p_object_name in per_competence_elements.object_name%type
4086: ,p_object_id in per_competence_elements.object_id%type
4087: ,p_party_id in per_competence_elements.party_id%type -- HR/TCA merge
4088: ,p_qualification_type_id in per_competence_elements.qualification_type_id%type
4089: ) is
4090: --
4091: l_proc varchar2(72):=
4092: g_package||'chk_unique_competence_element';
4096: --
4097: begin
4098: hr_utility.set_location('Entering:'|| l_proc, 1);
4099: if hr_multi_message.no_exclusive_error
4100: (p_check_column1 => 'PER_COMPETENCE_ELEMENTS.TYPE') then
4101: --
4102: -- Check mandatory parameters have being set.
4103: --
4104: hr_api.mandatory_arg_error
4139: --
4140: -- build the NATIVE dynamic SQL
4141: -- note: native dynamic SQL has been used for performance
4142: l_sql_stmt := 'SELECT NULL '||
4143: 'FROM per_competence_elements '||
4144: 'WHERE business_group_id = :p_business_group_id '||
4145: 'AND type = :p_type ';
4146: -- evaluate each bind determining the predicate
4147: -- note: if the bind is null we still add a predicate
4376: l_exists varchar2(1);
4377: --
4378: cursor csr_unique_comp_qual is
4379: select 'x'
4380: from per_competence_elements
4381: where type = 'QUALIFICATION'
4382: and competence_id = p_competence_id
4383: and qualification_type_id = p_qualification_type_id
4384: and p_effective_date between effective_date_from
4468: --
4469: --
4470: procedure chk_comp_element_delete
4471: ( p_competence_element_id
4472: in per_competence_elements.competence_element_id%TYPE
4473: ,p_business_group_id
4474: in per_competence_elements.business_group_id%TYPE
4475: ,p_parent_competence_element_id
4476: in per_competence_elements.parent_competence_element_id%TYPE
4470: procedure chk_comp_element_delete
4471: ( p_competence_element_id
4472: in per_competence_elements.competence_element_id%TYPE
4473: ,p_business_group_id
4474: in per_competence_elements.business_group_id%TYPE
4475: ,p_parent_competence_element_id
4476: in per_competence_elements.parent_competence_element_id%TYPE
4477: ,p_type
4478: in per_competence_elements.type%TYPE
4472: in per_competence_elements.competence_element_id%TYPE
4473: ,p_business_group_id
4474: in per_competence_elements.business_group_id%TYPE
4475: ,p_parent_competence_element_id
4476: in per_competence_elements.parent_competence_element_id%TYPE
4477: ,p_type
4478: in per_competence_elements.type%TYPE
4479: ,p_competence_type
4480: in per_competence_elements.competence_type%TYPE
4474: in per_competence_elements.business_group_id%TYPE
4475: ,p_parent_competence_element_id
4476: in per_competence_elements.parent_competence_element_id%TYPE
4477: ,p_type
4478: in per_competence_elements.type%TYPE
4479: ,p_competence_type
4480: in per_competence_elements.competence_type%TYPE
4481: ,p_group_competence_type
4482: in per_competence_elements.group_competence_type%TYPE
4476: in per_competence_elements.parent_competence_element_id%TYPE
4477: ,p_type
4478: in per_competence_elements.type%TYPE
4479: ,p_competence_type
4480: in per_competence_elements.competence_type%TYPE
4481: ,p_group_competence_type
4482: in per_competence_elements.group_competence_type%TYPE
4483: ,p_assessment_id
4484: in per_competence_elements.assessment_id%TYPE
4478: in per_competence_elements.type%TYPE
4479: ,p_competence_type
4480: in per_competence_elements.competence_type%TYPE
4481: ,p_group_competence_type
4482: in per_competence_elements.group_competence_type%TYPE
4483: ,p_assessment_id
4484: in per_competence_elements.assessment_id%TYPE
4485: ,p_assessment_type_id
4486: in per_competence_elements.assessment_type_id%TYPE
4480: in per_competence_elements.competence_type%TYPE
4481: ,p_group_competence_type
4482: in per_competence_elements.group_competence_type%TYPE
4483: ,p_assessment_id
4484: in per_competence_elements.assessment_id%TYPE
4485: ,p_assessment_type_id
4486: in per_competence_elements.assessment_type_id%TYPE
4487: ,p_competence_id
4488: in per_competence_elements.competence_id%TYPE
4482: in per_competence_elements.group_competence_type%TYPE
4483: ,p_assessment_id
4484: in per_competence_elements.assessment_id%TYPE
4485: ,p_assessment_type_id
4486: in per_competence_elements.assessment_type_id%TYPE
4487: ,p_competence_id
4488: in per_competence_elements.competence_id%TYPE
4489: ) is
4490: --
4484: in per_competence_elements.assessment_id%TYPE
4485: ,p_assessment_type_id
4486: in per_competence_elements.assessment_type_id%TYPE
4487: ,p_competence_id
4488: in per_competence_elements.competence_id%TYPE
4489: ) is
4490: --
4491: l_proc varchar2(72):= g_package||'chk_comp_element_delete';
4492: l_exists varchar2(1);
4498: -- Maybe the business rules need tighting up around here as maybe the form should change.
4499: --
4500: cursor csr_get_used_comp_element is
4501: select null
4502: from per_competence_elements
4503: where type = 'ASSESSMENT'
4504: and competence_id = p_competence_id
4505: and assessment_id in
4506: (Select asn.assessment_id
4506: (Select asn.assessment_id
4507: From per_assessments asn
4508: Where asn.assessment_type_id =
4509: (Select assessment_type_id
4510: From per_competence_elements
4511: Where competence_element_id =
4512: (Select parent_competence_element_id
4513: From per_competence_elements
4514: Where competence_element_id=p_competence_element_id
4509: (Select assessment_type_id
4510: From per_competence_elements
4511: Where competence_element_id =
4512: (Select parent_competence_element_id
4513: From per_competence_elements
4514: Where competence_element_id=p_competence_element_id
4515: )
4516: )
4517: )
4520: -- the parent of other competence element.
4521: --
4522: cursor csr_is_parent_comp is
4523: select null
4524: from per_competence_elements
4525: where parent_competence_element_id = p_competence_element_id
4526: and business_group_id = p_business_group_id;
4527: --
4528: -- Cursor to check the COMPETENCE_USAGE' type referenced by
4529: -- competence element of type "ASSESSMENT_COMPETENCE'
4530: --
4531: cursor csr_get_comp_group is
4532: select null
4533: from per_competence_elements comp1
4534: where comp1.type = 'ASSESSMENT_COMPETENCE'
4535: and comp1.parent_competence_element_id is not null
4536: and comp1.business_group_id = p_business_group_id
4537: and comp1.competence_id = p_competence_id
4535: and comp1.parent_competence_element_id is not null
4536: and comp1.business_group_id = p_business_group_id
4537: and comp1.competence_id = p_competence_id
4538: and exists (select null
4539: from per_competence_elements comp2
4540: where comp2.competence_element_id =
4541: comp1.parent_competence_element_id
4542: and comp1.business_group_id =
4543:
4732: -- b) During insert.
4733: --
4734: hr_dflex_utility.ins_or_upd_descflex_attribs
4735: (p_appl_short_name => 'PER'
4736: ,p_descflex_name => 'PER_COMPETENCE_ELEMENTS'
4737: ,p_attribute_category => p_rec.attribute_category
4738: ,p_attribute1_name => 'ATTRIBUTE1'
4739: ,p_attribute1_value => p_rec.attribute1
4740: ,p_attribute2_name => 'ATTRIBUTE2'
5739: --
5740: cursor csr_leg_code is
5741: select legislation_code
5742: from per_business_groups pbg,
5743: per_competence_elements pce
5744: where pce.competence_element_id = p_competence_element_id
5745: and pbg.business_group_id = pce.business_group_id;
5746:
5747: l_proc varchar2(72) := g_package||'return_legislation_code';
5757: p_argument => 'competence_element_id',
5758: p_argument_value => p_competence_element_id );
5759: --
5760: Select 'Y' into l_business_group_flag
5761: from per_competence_elements
5762: where competence_element_id = p_competence_element_id
5763: and business_group_id is null;
5764:
5765:
5788: -- The primary key is invalid therefore we must error out
5789: --
5790: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
5791: hr_multi_message.add
5792: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ELEMENT_ID');
5793: hr_utility.raise_error;
5794: end if;
5795: --
5796: close csr_leg_code;