[Home] [Help]
21: --
22: l_proc varchar2(72) := g_package||'check_non_updateable_args';
23: --
24: Begin
25: hr_utility.set_location('Entering:'||l_proc, 5);
26: --
27: -- Only proceed with validation if a row exists for
28: -- the current record in the HR Schema
29:
31: if not per_cel_shd.api_updating
32: (p_competence_element_id => p_rec.competence_element_id
33: ,p_object_version_number => p_rec.object_version_number
34: ) then
35: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
36: hr_utility.set_message_token('PROCEDURE', l_proc);
37: hr_utility.set_message_token('STEP', '5');
38: end if;
39: --
32: (p_competence_element_id => p_rec.competence_element_id
33: ,p_object_version_number => p_rec.object_version_number
34: ) then
35: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
36: hr_utility.set_message_token('PROCEDURE', l_proc);
37: hr_utility.set_message_token('STEP', '5');
38: end if;
39: --
40: hr_utility.set_location(l_proc, 6);
33: ,p_object_version_number => p_rec.object_version_number
34: ) then
35: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
36: hr_utility.set_message_token('PROCEDURE', l_proc);
37: hr_utility.set_message_token('STEP', '5');
38: end if;
39: --
40: hr_utility.set_location(l_proc, 6);
41: --
36: hr_utility.set_message_token('PROCEDURE', l_proc);
37: hr_utility.set_message_token('STEP', '5');
38: end if;
39: --
40: hr_utility.set_location(l_proc, 6);
41: --
42: if p_rec.business_group_id <> per_cel_shd.g_old_rec.business_group_id then
43: hr_api.argument_changed_error
44: (p_api_name => l_proc
45: ,p_argument => 'BUSINESS_GROUP_ID'
46: ,p_base_table => per_cel_shd.g_tab_nam
47: );
48: end if;
49: hr_utility.set_location(l_proc, 7);
50: --
51: if p_rec.competence_id <> per_cel_shd.g_old_rec.competence_id then
52: hr_api.argument_changed_error
53: (p_api_name => l_proc
54: ,p_argument => 'COMPETENCE_ID'
55: ,p_base_table => per_cel_shd.g_tab_nam
56: );
57: end if;
58: hr_utility.set_location(l_proc, 8);
59: --
60: if p_rec.competence_element_id <>
61: per_cel_shd.g_old_rec.competence_element_id then
62: hr_api.argument_changed_error
65: ,p_base_table => per_cel_shd.g_tab_nam
66: );
67:
68: end if;
69: hr_utility.set_location(l_proc, 9);
70: --
71: if p_rec.parent_competence_element_id <>
72: per_cel_shd.g_old_rec.parent_competence_element_id then
73: hr_api.argument_changed_error
75: ,p_argument => 'PARENT_COMPETENCE_ELEMENT_ID'
76: ,p_base_table => per_cel_shd.g_tab_nam
77: );
78: end if;
79: hr_utility.set_location(l_proc, 10);
80: --
81: if p_rec.activity_version_id <> per_cel_shd.g_old_rec.activity_version_id then
82: hr_api.argument_changed_error
83: (p_api_name => l_proc
84: ,p_argument => 'ACTIVITY_VERSION_ID'
85: ,p_base_table => per_cel_shd.g_tab_nam
86: );
87: end if;
88: hr_utility.set_location(l_proc, 13);
89: --
90: hr_utility.set_location(l_proc, 14);
91: --
92: if p_rec.person_id <> per_cel_shd.g_old_rec.person_id then
86: );
87: end if;
88: hr_utility.set_location(l_proc, 13);
89: --
90: hr_utility.set_location(l_proc, 14);
91: --
92: if p_rec.person_id <> per_cel_shd.g_old_rec.person_id then
93: hr_api.argument_changed_error
94: (p_api_name => l_proc
106: ,p_base_table => per_cel_shd.g_tab_nam
107: );
108: end if;
109:
110: hr_utility.set_location(l_proc, 15);
111: --
112: if p_rec.job_id <> per_cel_shd.g_old_rec.job_id then
113: hr_api.argument_changed_error
114: (p_api_name => l_proc
116: ,p_base_table => per_cel_shd.g_tab_nam
117: );
118:
119: end if;
120: hr_utility.set_location(l_proc, 16);
121: --
122: if p_rec.valid_grade_id <> per_cel_shd.g_old_rec.valid_grade_id then
123: hr_api.argument_changed_error
124: (p_api_name => l_proc
125: ,p_argument => 'VALID_GRADE_ID'
126: ,p_base_table => per_cel_shd.g_tab_nam
127: );
128: end if;
129: hr_utility.set_location(l_proc, 16);
130: --
131: if p_rec.organization_id <> per_cel_shd.g_old_rec.organization_id then
132: hr_api.argument_changed_error
133: (p_api_name => l_proc
134: ,p_argument => 'ORGANIZATION_ID'
135: ,p_base_table => per_cel_shd.g_tab_nam
136: );
137: end if;
138: hr_utility.set_location(l_proc, 17);
139:
140: --
141: if p_rec.assessment_id <> per_cel_shd.g_old_rec.assessment_id then
142: hr_api.argument_changed_error
144: ,p_argument => 'ASSESSMENT_ID'
145: ,p_base_table => per_cel_shd.g_tab_nam
146: );
147: end if;
148: hr_utility.set_location(l_proc, 18);
149: --
150: --
151: if p_rec.enterprise_id <> per_cel_shd.g_old_rec.enterprise_id then
152: hr_api.argument_changed_error
154: ,p_argument => 'ENTERPRISE_ID'
155: ,p_base_table => per_cel_shd.g_tab_nam
156: );
157: end if;
158: hr_utility.set_location(l_proc, 20);
159:
160: --
161: --
162: if p_rec.position_id <> per_cel_shd.g_old_rec.position_id then
165: ,p_argument => 'POSITION_ID'
166: ,p_base_table => per_cel_shd.g_tab_nam
167: );
168: end if;
169: hr_utility.set_location(l_proc, 22);
170: --
171: if p_rec.type <> per_cel_shd.g_old_rec.type then
172: hr_api.argument_changed_error
173: (p_api_name => l_proc
174: ,p_argument => 'TYPE'
175: ,p_base_table => per_cel_shd.g_tab_nam
176: );
177: end if;
178: hr_utility.set_location(l_proc, 25);
179: --
180: if p_rec.object_id <> per_cel_shd.g_old_rec.object_id then
181: hr_api.argument_changed_error
182: (p_api_name => l_proc
241: l_api_updating boolean;
242:
243: --
244: begin
245: hr_utility.set_location('Entering:'|| l_proc, 1);
246: --
247: -- Check mandatory parameters have being set.
248: --
249: hr_api.mandatory_arg_error
264: if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.mandatory,
265: hr_api.g_varchar2) <>
266: nvl(p_mandatory,hr_api.g_varchar2))
267: OR not l_api_updating ) then
268: hr_utility.set_location(l_proc, 6);
269: --
270: -- check that the p_mandatory exists in hr_lookups.
271: --
272: if (p_mandatory IS NOT NULL ) then
275: ,p_lookup_type => 'YES_NO'
276: ,p_lookup_code => p_mandatory
277: ) then
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:
276: ,p_lookup_code => p_mandatory
277: ) then
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;
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:
287: --
288: end if;
287: --
288: end if;
289: end if;
290: --
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'
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;
300: hr_utility.set_location(' Leaving:'||l_proc,25);
301: end chk_mandatory;
296: ) then
297: hr_utility.set_location(' Leaving:'||l_proc,20);
298: raise;
299: end if;
300: hr_utility.set_location(' Leaving:'||l_proc,25);
301: end chk_mandatory;
302: --
303: ------------------------------------------------------------------------------
304: -- |--------------------------
346: l_api_updating boolean;
347: --
348: begin
349:
350: hr_utility.set_location('Entering:'|| l_proc, 1);
351: --
352: -- Check mandatory parameters have being set.
353: --
354: hr_api.mandatory_arg_error
368: if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.certification_method,
369: hr_api.g_varchar2) <>
370: nvl(p_certification_method,hr_api.g_varchar2))
371: OR not l_api_updating ) then
372: hr_utility.set_location(l_proc, 6);
373: --
374: -- check that the p_certification_method exists in hr_lookups.
375: --
376:
380: ,p_lookup_type => 'CERTIFICATION_METHOD'
381: ,p_lookup_code => p_certification_method
382: ) then
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;
381: ,p_lookup_code => p_certification_method
382: ) then
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;
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;
392: --
391: end if;
392: --
393: end if;
394: --
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'
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;
404: hr_utility.set_location(' Leaving:'||l_proc,25);
405: end chk_certification_method;
400: ) then
401: hr_utility.set_location(' Leaving:'||l_proc,20);
402: raise;
403: end if;
404: hr_utility.set_location(' Leaving:'||l_proc,25);
405: end chk_certification_method;
406: --
407: --
408: ------------------------------------------------------------------------------
451: l_api_updating boolean;
452: --
453:
454: begin
455: hr_utility.set_location('Entering:'|| l_proc, 1);
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
473: nvl(p_certification_method,hr_api.g_varchar2)) OR
474: (nvl(per_cel_shd.g_old_rec.certification_date , hr_api.g_date ) <>
475: nvl(p_certification_date,hr_api.g_date)))
476: OR not l_api_updating ) then
477: hr_utility.set_location(l_proc, 6);
478: --
479: -- if the certification_date is null and certification_method is
480: -- not null then raise an error
481: --
481: --
482: if ( (p_certification_method is NOT NULL)
483: and (p_certification_date is NULL) )
484: then
485: hr_utility.set_location(l_proc,10);
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
482: if ( (p_certification_method is NOT NULL)
483: and (p_certification_date is NULL) )
484: then
485: hr_utility.set_location(l_proc,10);
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'
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:
496: end if;
497: --
498: if ( (p_certification_method is NULL)
497: --
498: if ( (p_certification_method is NULL)
499: and (p_certification_date is NOT NULL) )
500: then
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'
498: if ( (p_certification_method is NULL)
499: and (p_certification_date is NOT NULL) )
500: then
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'
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;
511: --
512: hr_utility.set_location('Leaving: '||l_proc,15);
513: end if;
508:
509: hr_utility.raise_error;
510: end if;
511: --
512: hr_utility.set_location('Leaving: '||l_proc,15);
513: 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;
523: end if;
524: hr_utility.set_location(' Leaving:'||l_proc,25);
525: end chk_certification_method_date;
520: ) then
521: hr_utility.set_location(' Leaving:'||l_proc,20);
522: raise;
523: end if;
524: hr_utility.set_location(' Leaving:'||l_proc,25);
525: end chk_certification_method_date;
526: --
527: --
528: ----------------------------------------------------------------------------
573: --
574:
575:
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: --
591: OR
592: (l_api_updating AND (nvl(per_cel_shd.g_old_rec.next_certification_date,
593: hr_api.g_date)<> nvl(p_next_certification_date,hr_api.g_date))
594: OR not l_api_updating))
595: then hr_utility.set_location(l_proc, 6);
596: --
597: -- Raise error if certification date is NULL
598: -- or next certification date occurs before certification date
599: --
603: OR
604: (p_certification_date is NOT NULL AND
605: p_next_certification_date <= p_certification_date)) THEN
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;
604: (p_certification_date is NOT NULL AND
605: p_next_certification_date <= p_certification_date)) THEN
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;
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
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;
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;
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
624:
625: end if;
626: --
627: end if; -- end for if checking for no_exclusive_error for CERTIFICATION_DATE
628: --
629: hr_utility.set_location('Leaving:'||l_proc,15);
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);
639: raise;
640: end if;
641: hr_utility.set_location(' Leaving:'||l_proc,25);
642: end chk_next_certification_date;
637: ) then
638: hr_utility.set_location(' Leaving:'||l_proc,20);
639: raise;
640: end if;
641: hr_utility.set_location(' Leaving:'||l_proc,25);
642: end chk_next_certification_date;
643: --
644: --
645: ------------------------------------------------------------------------------
686:
687: l_api_updating boolean;
688: --
689: begin
690: hr_utility.set_location('Entering:'|| l_proc, 1);
691: --
692: -- Check mandatory parameters have being set.
693: --
694: hr_api.mandatory_arg_error
710: hr_api.g_varchar2) <>
711: nvl(p_competence_type,hr_api.g_varchar2))
712: OR not l_api_updating ) then
713:
714: hr_utility.set_location(l_proc, 6);
715: --
716: -- check that the p_competence_type exists in hr_lookups.
717: --
718: if (p_competence_type IS NOT NULL ) then
721: ,p_lookup_type => 'COMPETENCE_TYPE'
722: ,p_lookup_code => p_competence_type
723: ) then
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:
722: ,p_lookup_code => p_competence_type
723: ) then
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;
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: --
733: end if;
734: --
733: end if;
734: --
735: end if;
736: --
737: hr_utility.set_location(' Leaving:'|| l_proc, 15);
738: exception
739: when app_exception.application_exception then
740: if hr_multi_message.exception_add
741: (p_associated_column1 =>
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;
747: hr_utility.set_location(' Leaving:'||l_proc,25);
748: end chk_competence_type;
743: ) then
744: hr_utility.set_location(' Leaving:'||l_proc,20);
745: raise;
746: end if;
747: hr_utility.set_location(' Leaving:'||l_proc,25);
748: end chk_competence_type;
749: --
750: --
751: ------------------------------------------------------------------------------
793: g_package||'chk_source_of_proficiency';
794: l_api_updating boolean;
795: --
796: begin
797: hr_utility.set_location('Entering:'|| l_proc, 1);
798: --
799: -- Check mandatory parameters have being set.
800: --
801: hr_api.mandatory_arg_error
819: hr_api.g_varchar2) <>
820:
821: nvl(p_source_of_proficiency_level,hr_api.g_varchar2))
822: OR not l_api_updating ) then
823: hr_utility.set_location(l_proc, 6);
824: --
825: -- check that the p_source_of_proficiency exists in hr_lookups.
826: --
827: if (p_source_of_proficiency_level IS NOT NULL ) then
831: ,p_lookup_code => p_source_of_proficiency_level
832: ) then
833: -- Error: Invalid source_of_proficiency
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:
832: ) then
833: -- Error: Invalid source_of_proficiency
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;
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: --
843: end if;
844: --
843: end if;
844: --
845: end if;
846: --
847: hr_utility.set_location(' Leaving:'|| l_proc, 15);
848: exception
849: when app_exception.application_exception then
850: if hr_multi_message.exception_add
851: (p_associated_column1 =>
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;
857: hr_utility.set_location(' Leaving:'||l_proc,25);
858: end chk_source_of_proficiency;
853: ) then
854: hr_utility.set_location(' Leaving:'||l_proc,20);
855: raise;
856: end if;
857: hr_utility.set_location(' Leaving:'||l_proc,25);
858: end chk_source_of_proficiency;
859: --
860: --
861: --
904: l_proc varchar2(72):= g_package||'chk_type';
905: l_api_updating boolean;
906: --
907: begin
908: hr_utility.set_location('Entering:'|| l_proc, 1);
909: --
910: -- Check mandatory parameters have being set.
911: --
912: --
927: (p_competence_element_id => p_competence_element_id
928:
929: ,p_object_version_number => p_object_version_number);
930: --
931: hr_utility.set_location(l_proc, 6);
932: --
933: -- check that the p_type exists in hr_lookups.
934: --
935: if (NOT l_api_updating) then
940: ,p_lookup_code => p_type
941: ) then
942:
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;
941: ) then
942:
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:
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: --
952: end if;
952: end if;
953: --
954: end if;
955: --
956: hr_utility.set_location(' Leaving:'|| l_proc, 15);
957: exception
958: when app_exception.application_exception then
959: if hr_multi_message.exception_add
960: (p_associated_column1 =>
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;
966: hr_utility.set_location(' Leaving:'||l_proc,25);
967: end chk_type;
962: ) then
963: hr_utility.set_location(' Leaving:'||l_proc,20);
964: raise;
965: end if;
966: hr_utility.set_location(' Leaving:'||l_proc,25);
967: end chk_type;
968:
969: -------------------------------------------------------------------------------
970: -----------------------------< chk_foreign_keys >------------------------------
1297:
1298:
1299: --
1300: begin
1301: hr_utility.set_location('Entering:'|| l_proc, 1);
1302: --
1303: -- Check mandatory parameters have being set.
1304: -- ngundura should check only if object_type is not
1305: -- PROJECT_ROLE AND OPEN_ASSIGNMENT
1320: ,p_object_version_number => p_object_version_number);
1321: --
1322: IF ( NOT l_api_updating ) then
1323:
1324: hr_utility.set_location(l_proc, 6);
1325: --
1326: -- Check that the person_id exists in the correct BG.
1327: --
1328: if(p_person_id IS NOT NULL) then
1325: --
1326: -- Check that the person_id exists in the correct BG.
1327: --
1328: if(p_person_id IS NOT NULL) then
1329: --hr_utility.set_location(l_proc, 7);
1330: open csr_valid_person_id;
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);
1329: --hr_utility.set_location(l_proc, 7);
1330: open csr_valid_person_id;
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:
1340: p_business_group_id <> l_business_group_id then
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
1356:
1357: -- check that the activity_version_id is valid
1358: --
1359: if(p_activity_version_id IS NOT NULL) then
1360: hr_utility.set_location(l_proc, 40);
1361: open csr_valid_activity_id;
1362: fetch csr_valid_activity_id into l_business_group_id,
1363: l_start_date,
1364: l_end_date;
1363: l_start_date,
1364: l_end_date;
1365:
1366: if csr_valid_activity_id%notfound then
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'
1364: l_end_date;
1365:
1366: if csr_valid_activity_id%notfound then
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: );
1376: if p_business_group_id <> l_business_group_id then
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
1384: l_end_date < nvl(p_effective_date_to, l_end_date) then
1385: --
1386: -- The activity_version is not date valid
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 =>
1401: --
1402: -- check that the job_id is valid
1403: --
1404: if(p_job_id IS NOT NULL) then
1405: hr_utility.set_location(l_proc, 80);
1406: open csr_valid_job_id;
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);
1405: hr_utility.set_location(l_proc, 80);
1406: open csr_valid_job_id;
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;
1416:
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
1436: --
1437: -- check that the valid_grade_id is valid
1438: --
1439: if(p_valid_grade_id IS NOT NULL) then
1440: hr_utility.set_location(l_proc, 81);
1441: open csr_valid_grade_id;
1442: fetch csr_valid_grade_id into l_business_group_id,l_start_date,l_end_date;
1443: if csr_valid_grade_id%notfound then
1444: hr_utility.set_location(l_proc, 82);
1440: hr_utility.set_location(l_proc, 81);
1441: open csr_valid_grade_id;
1442: fetch csr_valid_grade_id into l_business_group_id,l_start_date,l_end_date;
1443: if csr_valid_grade_id%notfound then
1444: hr_utility.set_location(l_proc, 82);
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
1442: fetch csr_valid_grade_id into l_business_group_id,l_start_date,l_end_date;
1443: if csr_valid_grade_id%notfound then
1444: hr_utility.set_location(l_proc, 82);
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: );
1443: if csr_valid_grade_id%notfound then
1444: hr_utility.set_location(l_proc, 82);
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
1455:
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
1476: -- check that the position_id is valid
1477: --
1478: if(p_position_id IS NOT NULL) then
1479:
1480: hr_utility.set_location(l_proc, 95);
1481: open csr_valid_position_id;
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);
1480: hr_utility.set_location(l_proc, 95);
1481: open csr_valid_position_id;
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;
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
1509: --
1510: -- check that the organization_id is valid
1511: --
1512: if(p_organization_id IS NOT NULL) then
1513: hr_utility.set_location(l_proc, 110);
1514: open csr_valid_organization_id;
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);
1513: hr_utility.set_location(l_proc, 110);
1514: open csr_valid_organization_id;
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;
1523: p_business_group_id <> l_business_group_id then
1524: --
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) >
1543: --
1544: -- check that the assessment_id is valid
1545: --
1546: if(p_assessment_id IS NOT NULL) then
1547: hr_utility.set_location(l_proc, 125);
1548: open csr_valid_assessment_id;
1549: fetch csr_valid_assessment_id into l_business_group_id;
1550:
1551: if csr_valid_assessment_id%notfound then
1548: open csr_valid_assessment_id;
1549: fetch csr_valid_assessment_id into l_business_group_id;
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;
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: --
1571: -- check that the assessment_type_id is valid
1572: --
1573: --
1574: if (p_assessment_type_id IS NOT NULL) then
1575: hr_utility.set_location(l_proc, 151);
1576: open csr_valid_assessment_type_id;
1577: fetch csr_valid_assessment_type_id into l_business_group_id;
1578: if csr_valid_assessment_type_id%notfound then
1579:
1576: open csr_valid_assessment_type_id;
1577: fetch csr_valid_assessment_type_id into l_business_group_id;
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;
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: --
1597: --
1598: -- check that the parent_comp_elements_id is valid
1599: --
1600: if(p_parent_competence_element_id IS NOT NULL) then
1601: hr_utility.set_location(l_proc, 155);
1602: open csr_valid_comp_id;
1603: fetch csr_valid_comp_id into l_business_group_id;
1604: if csr_valid_comp_id%notfound then
1605: hr_utility.set_location(l_proc, 160);
1601: hr_utility.set_location(l_proc, 155);
1602: open csr_valid_comp_id;
1603: fetch csr_valid_comp_id into l_business_group_id;
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
1613: --
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:
1626: --
1627: -- check that the high_proficiency_level_id is valid
1628: --
1629: if(p_high_proficiency_level_id IS NOT NULL) then
1630: hr_utility.set_location(l_proc, 205);
1631: open csr_val_prof_id(p_high_proficiency_level_id);
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);
1630: hr_utility.set_location(l_proc, 205);
1631: open csr_val_prof_id(p_high_proficiency_level_id);
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:
1642: --
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: --
1653: --
1654: -- check that the proficiency_level_id is valid
1655: --
1656: if(p_proficiency_level_id IS NOT NULL) then
1657: hr_utility.set_location(l_proc, 220);
1658: open csr_val_prof_id(p_proficiency_level_id);
1659: hr_utility.set_location(l_proc, 221);
1660: fetch csr_val_prof_id into l_business_group_id;
1661: if csr_val_prof_id%notfound then
1655: --
1656: if(p_proficiency_level_id IS NOT NULL) then
1657: hr_utility.set_location(l_proc, 220);
1658: open csr_val_prof_id(p_proficiency_level_id);
1659: hr_utility.set_location(l_proc, 221);
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;
1658: open csr_val_prof_id(p_proficiency_level_id);
1659: hr_utility.set_location(l_proc, 221);
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:
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;
1669: hr_utility.set_location(l_proc, 227);
1670: -- ngundura changes done for pa requirements
1671: if
1665: else
1666:
1667: hr_utility.set_location(l_proc, 226);
1668: close csr_val_prof_id;
1669: hr_utility.set_location(l_proc, 227);
1670: -- ngundura changes done for pa requirements
1671: if
1672: p_business_group_id <> l_business_group_id and l_business_group_id is not null then
1673: --
1673: --
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:
1684: --
1685: -- check that the weighting_level_id is valid
1686: --
1687: if(p_weighting_level_id IS NOT NULL) then
1688: hr_utility.set_location(l_proc, 235);
1689: open csr_val_prof_id(p_weighting_level_id);
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);
1688: hr_utility.set_location(l_proc, 235);
1689: open csr_val_prof_id(p_weighting_level_id);
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
1700: --
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: --
1711: --
1712: -- check that the rating_level_id is valid
1713: --
1714: if(p_rating_level_id IS NOT NULL) then
1715: hr_utility.set_location(l_proc, 250);
1716: open csr_val_prof_id(p_rating_level_id);
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);
1715: hr_utility.set_location(l_proc, 250);
1716: open csr_val_prof_id(p_rating_level_id);
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:
1727: p_business_group_id <> l_business_group_id and l_business_group_id is not null then
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: --
1738:
1739: -- check that the competence_id is valid
1740: --
1741: if(p_competence_id IS NOT NULL) then
1742: hr_utility.set_location(l_proc, 265);
1743: if p_type = 'COMPETENCE_USAGE' OR p_type = 'ASSESSMENT_GROUP' OR
1744: p_type = 'ASSESSMENT' OR p_type= 'ASSESSMENT_COMPETENCE' then
1745: open csr_usage_competence_id;
1746: fetch csr_usage_competence_id into l_business_group_id;
1760: end if;
1761: end if;
1762: else
1763: open csr_valid_competence_id;
1764: hr_utility.set_location(l_proc, 266);
1765: fetch csr_valid_competence_id into l_business_group_id,l_start_date,l_end_date;
1766: hr_utility.set_location(l_proc, 267);
1767: if csr_valid_competence_id%notfound then
1768: hr_utility.set_location(l_proc, 270);
1762: else
1763: open csr_valid_competence_id;
1764: hr_utility.set_location(l_proc, 266);
1765: fetch csr_valid_competence_id into l_business_group_id,l_start_date,l_end_date;
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');
1764: hr_utility.set_location(l_proc, 266);
1765: fetch csr_valid_competence_id into l_business_group_id,l_start_date,l_end_date;
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);
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
1775: -- added the AND condition in the following if
1776: if
1777: p_business_group_id <> l_business_group_id and l_business_group_id is not null then
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
1803: --
1804: -- check that the high_proficiency_level_id is valid
1805: --
1806: if(p_high_proficiency_level_id IS NOT NULL) then
1807: hr_utility.set_location(l_proc, 280);
1808:
1809: open csr_val_prof_id(p_high_proficiency_level_id);
1810: fetch csr_val_prof_id into l_business_group_id;
1811: if csr_val_prof_id%notfound then
1808:
1809: open csr_val_prof_id(p_high_proficiency_level_id);
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;
1821: -- The high_proficiency_level_id exists in a
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: --
1831: --
1832: -- check that the proficiency_level_id is valid
1833: --
1834: if(p_proficiency_level_id IS NOT NULL) then
1835: hr_utility.set_location(l_proc, 295);
1836: open csr_val_prof_id(p_proficiency_level_id);
1837:
1838: fetch csr_val_prof_id into l_business_group_id;
1839: if csr_val_prof_id%notfound then
1836: open csr_val_prof_id(p_proficiency_level_id);
1837:
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;
1850: -- The proficiency_level_id exists in a
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: --
1861: -- Check the enterprise_id is valid
1862: --
1863: --
1864: if(p_enterprise_id is not null ) then
1865: hr_utility.set_location(l_proc, 310);
1866: if (p_enterprise_id <> p_business_group_id) then
1867: hr_utility.set_message(800,'HR_52252_CEL_ENTPISE_ID_INVL');
1868: hr_utility.set_location(l_proc,315);
1869:
1863: --
1864: if(p_enterprise_id is not null ) then
1865: hr_utility.set_location(l_proc, 310);
1866: if (p_enterprise_id <> p_business_group_id) then
1867: hr_utility.set_message(800,'HR_52252_CEL_ENTPISE_ID_INVL');
1868: hr_utility.set_location(l_proc,315);
1869:
1870: end if;
1871: end if;
1864: if(p_enterprise_id is not null ) then
1865: hr_utility.set_location(l_proc, 310);
1866: if (p_enterprise_id <> p_business_group_id) then
1867: hr_utility.set_message(800,'HR_52252_CEL_ENTPISE_ID_INVL');
1868: hr_utility.set_location(l_proc,315);
1869:
1870: end if;
1871: end if;
1872: elsif l_api_updating then
1872: elsif l_api_updating then
1873: --
1874:
1875: if(p_person_id IS NOT NULL) then
1876: --hr_utility.set_location(l_proc, 7);
1877: open csr_valid_person_id;
1878: fetch csr_valid_person_id into l_business_group_id,l_start_date,l_end_date;
1879: close csr_valid_person_id;
1880: if invalid_person_dates(p_effective_date_from,p_effective_date_to) then
1889: --
1890: -- check that the activity_version_id is valid
1891: --
1892: if(p_activity_version_id IS NOT NULL) then
1893: hr_utility.set_location(l_proc, 330);
1894: open csr_valid_activity_id;
1895: fetch csr_valid_activity_id into l_business_group_id,
1896: l_start_date,
1897: l_end_date;
1900: l_end_date < nvl(p_effective_date_to, l_end_date) THEN
1901: --
1902: -- The activity_version is not date valid
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 =>
1916: --
1917: -- check that the job_id is valid
1918: --
1919: if(p_job_id IS NOT NULL) then
1920: hr_utility.set_location(l_proc, 340);
1921: open csr_valid_job_id;
1922: fetch csr_valid_job_id into l_business_group_id, l_start_date,l_end_date;
1923: close csr_valid_job_id;
1924: if (p_effective_date_from < l_start_date) OR
1935: --
1936: -- check that the valid_grade_id is valid
1937: --
1938: if(p_valid_grade_id IS NOT NULL) then
1939: hr_utility.set_location(l_proc, 350);
1940: open csr_valid_grade_id;
1941: fetch csr_valid_grade_id into l_business_group_id,l_start_date,l_end_date;
1942: close csr_valid_grade_id;
1943: if (p_effective_date_from < l_start_date) OR
1957: -- check that the position_id is valid
1958: --
1959: if(p_position_id IS NOT NULL) then
1960:
1961: hr_utility.set_location(l_proc, 360);
1962: open csr_valid_position_id;
1963: fetch csr_valid_position_id into l_business_group_id,l_start_date,l_end_date;
1964: close csr_valid_position_id;
1965: if (p_effective_date_from < l_start_date) OR
1977: --
1978: -- check that the organization_id is valid
1979: --
1980: if(p_organization_id IS NOT NULL) then
1981: hr_utility.set_location(l_proc, 370);
1982: open csr_valid_organization_id;
1983: fetch csr_valid_organization_id into l_business_group_id,l_start_date,l_end_date;
1984: close csr_valid_organization_id;
1985: if (p_effective_date_from < l_start_date) OR
1996: --
1997: -- check that the competence_id is valid
1998: --
1999: if(p_competence_id IS NOT NULL) then
2000: hr_utility.set_location(l_proc, 380);
2001: open csr_valid_competence_id;
2002: fetch csr_valid_competence_id into l_business_group_id,l_start_date,l_end_date;
2003: close csr_valid_competence_id;
2004: if (nvl(p_effective_date_from,hr_api.g_eot) <
2017: --
2018: -- check that the qualification_type_id is valid
2019: --
2020: if(p_qualification_type_id IS NOT NULL) then
2021: hr_utility.set_location(l_proc, 390);
2022:
2023: open csr_valid_qualification_type;
2024: fetch csr_valid_qualification_type into l_qualification_type_id;
2025:
2032: --
2033: end if;
2034: end if;
2035: --
2036: hr_utility.set_location('Leaving: '||l_proc, 400);
2037: --
2038:
2039: end chk_foreign_keys;
2040: --
2092: from hz_parties hzp
2093: where hzp.party_id = p_rec.party_id;
2094: --
2095: begin
2096: hr_utility.set_location('Entering:'|| l_proc, 1);
2097: --
2098: --
2099: if p_rec.person_id is not null then
2100: --
2100: --
2101: open csr_get_party_id;
2102: fetch csr_get_party_id into l_party_id;
2103: close csr_get_party_id;
2104: hr_utility.set_location(l_proc,20);
2105: if p_rec.party_id is not null then
2106: if p_rec.party_id <> nvl(l_party_id,-1) then
2107: hr_utility.set_message(800, 'HR_289343_PERSONPARTY_MISMATCH');
2108: hr_utility.set_location(l_proc,30);
2103: close csr_get_party_id;
2104: hr_utility.set_location(l_proc,20);
2105: if p_rec.party_id is not null then
2106: if p_rec.party_id <> nvl(l_party_id,-1) then
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'
2104: hr_utility.set_location(l_proc,20);
2105: if p_rec.party_id is not null then
2106: if p_rec.party_id <> nvl(l_party_id,-1) then
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 =>
2117: else -- if party_id is null
2118: --
2119: -- derive party_id from per_all_people_f using person_id
2120: --
2121: hr_utility.set_location(l_proc,50);
2122: p_rec.party_id := l_party_id;
2123: end if;
2124: --
2125: else -- if person_id is null
2128: open csr_valid_party_id;
2129: fetch csr_valid_party_id into l_party_id2;
2130: if csr_valid_party_id%notfound then
2131: close csr_valid_party_id;
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'
2129: fetch csr_valid_party_id into l_party_id2;
2130: if csr_valid_party_id%notfound then
2131: close csr_valid_party_id;
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: );
2144: end if; -- end if for if party is not null.
2145: --
2146: end if; -- end if for if person_id is null.
2147: --
2148: hr_utility.set_location(' Leaving:'||l_proc,100);
2149: End chk_party_id;
2150: --
2151: --
2152: ------------------------------------------------------------------------------
2266: --
2267: begin
2268: open csr_get_rating_scale(p_prof_id);
2269: fetch csr_get_rating_scale into p_step_val;
2270: hr_utility.set_location(l_proc,5);
2271:
2272: if csr_get_rating_scale%notfound then
2273: --
2274: -- raise an error. The proficiency_level_id must be invalid
2274: -- raise an error. The proficiency_level_id must be invalid
2275: --
2276: close csr_get_rating_scale;
2277: if p_error = 'HIGH_PROF' then
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'
2275: --
2276: close csr_get_rating_scale;
2277: if p_error = 'HIGH_PROF' then
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 =>
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);
2289: hr_multi_message.add
2290: (p_associated_column1 =>
2291: 'PER_COMPETENCE_ELEMENTS.PROFICIENCY_LEVEL_ID'
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);
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);
2298: else
2299: close csr_get_rating_scale;
2300: end if;
2301: hr_utility.set_location('LEAVING: ' ||l_proc,30);
2297: hr_utility.set_location(l_proc,25);
2298: else
2299: close csr_get_rating_scale;
2300: end if;
2301: hr_utility.set_location('LEAVING: ' ||l_proc,30);
2302: end validate_comp_levels;
2303: --
2304: begin
2305: hr_utility.set_location('Entering:'|| l_proc, 1);
2301: hr_utility.set_location('LEAVING: ' ||l_proc,30);
2302: end validate_comp_levels;
2303: --
2304: begin
2305: hr_utility.set_location('Entering:'|| l_proc, 1);
2306: --
2307: --
2308: -- Check mandatory parameters have being set.
2309: -- ngundura check for business group_id only if its business_group specific
2335: -- do nothing if both high and low proficiencies are null
2336: --
2337: if (p_high_proficiency_level_id is null AND
2338: p_proficiency_level_id is null) then
2339: hr_utility.set_location(l_proc,5);
2340: --
2341: -- raise error if the high_prof is set but not the proficiency_level.
2342: --
2343: elsif (p_proficiency_level_id is null AND
2341: -- raise error if the high_prof is set but not the proficiency_level.
2342: --
2343: elsif (p_proficiency_level_id is null AND
2344: p_high_proficiency_level_id is not null) then
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'
2342: --
2343: elsif (p_proficiency_level_id is null AND
2344: p_high_proficiency_level_id is not null) then
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 =>
2358: if p_competence_id is null then
2359: --
2360: -- issue an error message if the competence_id is null
2361: --
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'
2359: --
2360: -- issue an error message if the competence_id is null
2361: --
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: );
2371: if p_proficiency_level_id is not null then
2372: validate_comp_levels (p_prof_id => p_proficiency_level_id,
2373: p_error => 'PROF',
2374: p_step_val => l_step_value1);
2375: hr_utility.set_location(l_proc,15);
2376: --
2377: end if;
2378: if p_high_proficiency_level_id is not null then
2379: validate_comp_levels (p_prof_id => p_high_proficiency_level_id,
2378: if p_high_proficiency_level_id is not null then
2379: validate_comp_levels (p_prof_id => p_high_proficiency_level_id,
2380: p_error => 'HIGH_PROF',
2381: p_step_val => l_step_value2);
2382: hr_utility.set_location(l_proc,20);
2383: --
2384: end if;
2385: --
2386: --
2390: --
2391: if (p_high_proficiency_level_id is not null and p_proficiency_level_id
2392: is not null) then
2393: if(l_step_value1 > l_step_value2) then
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'
2391: if (p_high_proficiency_level_id is not null and p_proficiency_level_id
2392: is not null) then
2393: if(l_step_value1 > l_step_value2) then
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 =>
2406: --
2407: end if;
2408: --
2409: end if; -- end if for api_updating
2410: hr_utility.set_location('Leaving: ' || l_proc, 30);
2411: end chk_proficiency_level_id;
2412: --
2413: --
2414: ------------------------------------------------------------------------------
2498: and nvl(ast.business_group_id,ass.business_group_id+0) = ass.business_group_id+0
2499: and ass.business_group_id+0 = NVL(ral.business_group_id , ass.business_group_id);
2500: --
2501: begin
2502: hr_utility.set_location('Entering:'|| l_proc, 1);
2503: --
2504: --
2505: -- Check mandatory parameters have being set.
2506: -- ngundura check should only be for business group specific
2537: --
2538: -- do nothing if both rating and weighting are null
2539: --
2540: if (p_rating_level_id is null AND p_weighting_level_id is null) then
2541: hr_utility.set_location(l_proc,5);
2542: else
2543: if (p_assessment_id is null) then
2544: hr_utility.set_location(l_proc,10);
2545: hr_utility.set_message(801,'HR_51645_CEL_ASS_ASS_TP_NULL');
2540: if (p_rating_level_id is null AND p_weighting_level_id is null) then
2541: hr_utility.set_location(l_proc,5);
2542: else
2543: if (p_assessment_id is null) then
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'
2541: hr_utility.set_location(l_proc,5);
2542: else
2543: if (p_assessment_id is null) then
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 =>
2571: fetch csr_valid_rate_weight_id into l_rating_scale_id,
2572: l_weighting_scale_id;
2573: if csr_valid_rate_weight_id%notfound then
2574: close csr_valid_rate_weight_id;
2575: hr_utility.set_location(l_proc,15);
2576: hr_utility.set_message(801,'HR_51727_CEL_RATE_ASS_ID_INVL');
2577: --
2578: if p_rating_level_id is not null then
2579: --
2572: l_weighting_scale_id;
2573: if csr_valid_rate_weight_id%notfound then
2574: close csr_valid_rate_weight_id;
2575: hr_utility.set_location(l_proc,15);
2576: hr_utility.set_message(801,'HR_51727_CEL_RATE_ASS_ID_INVL');
2577: --
2578: if p_rating_level_id is not null then
2579: --
2580: hr_multi_message.add
2598: end if;
2599: --
2600: else
2601: close csr_valid_rate_weight_id;
2602: hr_utility.set_location(l_proc,20);
2603: end if; -- end if for csr_valid_rate_weight_id%notfound
2604: end if; -- end if for any of rating_level_id or weighting_level_id is not null.
2605: end if; -- end if for api_updating
2606: end if; -- end if for checking whether TYPE is assessment.
2604: end if; -- end if for any of rating_level_id or weighting_level_id is not null.
2605: end if; -- end if for api_updating
2606: end if; -- end if for checking whether TYPE is assessment.
2607: end if; -- end if for no_exclusive_error check for TYPE
2608: hr_utility.set_location('Leaving: ' || l_proc, 30);
2609: end chk_rating_weighting_id;
2610: --
2611: --
2612: ------------------------------------------------------------------------------
2699: csr_check_dates csr_check_dates_type;
2700: v_check_type varchar2(20);
2701: begin
2702: --
2703: hr_utility.set_location('Entering:'|| l_proc, 1);
2704: --
2705: if p_check_type = 'GRADE' then
2706: v_check_type := 'VALID_GRADE_ID';
2707: else
2721: p_competence_element_id,p_competence_element_id,p_valid_grade_null;
2722:
2723: fetch csr_check_dates into l_exists;
2724: if csr_check_dates%found then
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';
2723: fetch csr_check_dates into l_exists;
2724: if csr_check_dates%found then
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';
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);
2763: close csr_check_dates;
2764: end if;
2765: hr_utility.set_location('LEAVING: ' || l_proc,4);
2766: --
2761: else -- if no records are found in the cursor, close the cursor.
2762: hr_utility.set_location(l_proc,3);
2763: close csr_check_dates;
2764: end if;
2765: hr_utility.set_location('LEAVING: ' || l_proc,4);
2766: --
2767:
2768: end check_all_dates;
2769: --
2767:
2768: end check_all_dates;
2769: --
2770: begin
2771: hr_utility.set_location('Entering:'|| l_proc, 1);
2772: --
2773: --
2774: -- Check mandatory parameters have being set.
2775: -- ngundura this check should not be there for global competences
2800: -- check that the effective_date_from is before the effective_date_to
2801: --
2802: if (p_effective_date_from > nvl(p_effective_date_to,hr_api.g_eot))
2803: then
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');
2801: --
2802: if (p_effective_date_from > nvl(p_effective_date_to,hr_api.g_eot))
2803: then
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:
2812: -- Check that the dates for enterprise_id does not overlap.
2813: --
2814: check_all_dates('ENTERPRISE',p_enterprise_id,null,
2815: 801,'HR_52288_CEL_ENT_DATES_OVLAP');
2816: hr_utility.set_location(l_proc,13);
2817: elsif (p_person_id is not null) then
2818: --
2819: -- Check that the dates for person_id does not overlap.
2820: --
2819: -- Check that the dates for person_id does not overlap.
2820: --
2821: check_all_dates('PERSON',p_person_id,null,
2822: 801,'HR_51648_CEL_PER_DATES_OVLAP');
2823: hr_utility.set_location(l_proc,15);
2824: elsif (p_valid_grade_id is not null) then
2825: if p_job_id is not null then
2826: --
2827: -- Check that the dates from valid_grade_id do not overlap
2853: -- Check that the dates for job_id does not overlap.
2854: --
2855: check_all_dates('JOB',p_job_id,null,
2856: 801,'HR_51649_CEL_JOB_DATES_OVLAP');
2857: hr_utility.set_location(l_proc,20);
2858: --
2859: elsif (p_position_id is not null) then
2860: --
2861: -- Check that the dates for position_id does not overlap.
2861: -- Check that the dates for position_id does not overlap.
2862: --
2863: check_all_dates('POSITION',p_position_id,null,
2864: 801,'HR_51650_CEL_POS_DATES_OVLAP');
2865: hr_utility.set_location(l_proc,25);
2866: --
2867: elsif (p_organization_id is not null) then
2868: --
2869: -- Check that the dates for organization_id does not overlap.
2869: -- Check that the dates for organization_id does not overlap.
2870: --
2871: check_all_dates('ORGANIZATION',p_organization_id,null,
2872: 801,'HR_51651_CEL_ORG_DATES_OVLAP');
2873: hr_utility.set_location(l_proc,30);
2874: --
2875: end if;
2876: end if; -- end if for api_updating
2877: hr_utility.set_location('Leaving: ' || l_proc, 40);
2873: hr_utility.set_location(l_proc,30);
2874: --
2875: end if;
2876: end if; -- end if for api_updating
2877: hr_utility.set_location('Leaving: ' || l_proc, 40);
2878: end chk_competence_element_dates;
2879: --
2880: ------------------------------------------------------------------------------
2881: --|--------------------------< Chk_normal_elapse_duration >----------------|
2928: := g_package||'chk_normal_elapse_duration';
2929: l_api_updating boolean;
2930: --
2931: begin
2932: hr_utility.set_location('Entering:'|| l_proc, 1);
2933: --
2934:
2935: --
2936: -- Check mandatory parameters have being set.
2963: p_normal_elapse_duration_unit IS NULL) then
2964: --
2965: -- Do nothing if both are null.
2966: --
2967: hr_utility.set_location(l_proc, 5);
2968: elsif ((p_normal_elapse_duration IS NULL AND
2969: p_normal_elapse_duration_unit IS NOT NULL) OR
2970: (p_normal_elapse_duration IS NOT NULL AND
2971: p_normal_elapse_duration_unit IS NULL)) then
2971: p_normal_elapse_duration_unit IS NULL)) then
2972: --
2973: -- Raise an error if one is set but not the other
2974: --
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'
2973: -- Raise an error if one is set but not the other
2974: --
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: );
2991: ,p_lookup_code => p_normal_elapse_duration_unit
2992: )then
2993:
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: );
2992: )then
2993:
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;
2999: );
3000: end if;
3001: end if;
3002: end if;
3003: hr_utility.set_location('Leaving: ' || l_proc, 20);
3004: end chk_normal_elapse_duration;
3005: --
3006: --
3007: --
3113: where p_business_group_id + 0 = business_group_id
3114: and p_assessment_id = assessment_id;
3115: --
3116: begin
3117: hr_utility.set_location('Entering:'|| l_proc, 1);
3118: --
3119: --
3120: -- Check mandatory parameters have being set.
3121: -- ngundura this check should not be there for global competence elements
3149: -- Do nothing if the assessment_type and assessment_id are null
3150: --
3151: if (p_assessment_type_id is null and p_assessment_id is null) then
3152:
3153: hr_utility.set_location (l_proc,5);
3154: else
3155: if(p_assessment_type_id is null) then
3156: open csr_get_ass_type_id;
3157: fetch csr_get_ass_type_id into l_assessment_type_id;
3155: if(p_assessment_type_id is null) then
3156: open csr_get_ass_type_id;
3157: fetch csr_get_ass_type_id into l_assessment_type_id;
3158: if csr_get_ass_type_id%notfound then
3159: hr_utility.set_location (l_proc,10);
3160: close csr_get_ass_type_id;
3161: hr_utility.set_message(801,'HR_51748_CEL_ASS_TYPE_ID_INVL');
3162: hr_utility.raise_error;
3163: end if;
3157: fetch csr_get_ass_type_id into l_assessment_type_id;
3158: if csr_get_ass_type_id%notfound then
3159: hr_utility.set_location (l_proc,10);
3160: close csr_get_ass_type_id;
3161: hr_utility.set_message(801,'HR_51748_CEL_ASS_TYPE_ID_INVL');
3162: hr_utility.raise_error;
3163: end if;
3164: close csr_get_ass_type_id;
3165: --
3158: if csr_get_ass_type_id%notfound then
3159: hr_utility.set_location (l_proc,10);
3160: close csr_get_ass_type_id;
3161: hr_utility.set_message(801,'HR_51748_CEL_ASS_TYPE_ID_INVL');
3162: hr_utility.raise_error;
3163: end if;
3164: close csr_get_ass_type_id;
3165: --
3166:
3172: fetch csr_get_rating_scale_id into l_rating_scale_id,
3173: l_weighting_scale_id;
3174: if csr_get_rating_scale_id%notfound then
3175: close csr_get_rating_scale_id;
3176: hr_utility.set_location(l_proc,15);
3177: hr_utility.set_message(801,'HR_51748_CEL_ASS_TYPE_ID_INVL');
3178: hr_utility.raise_error;
3179: else
3180:
3173: l_weighting_scale_id;
3174: if csr_get_rating_scale_id%notfound then
3175: close csr_get_rating_scale_id;
3176: hr_utility.set_location(l_proc,15);
3177: hr_utility.set_message(801,'HR_51748_CEL_ASS_TYPE_ID_INVL');
3178: hr_utility.raise_error;
3179: else
3180:
3181: close csr_get_rating_scale_id;
3174: if csr_get_rating_scale_id%notfound then
3175: close csr_get_rating_scale_id;
3176: hr_utility.set_location(l_proc,15);
3177: hr_utility.set_message(801,'HR_51748_CEL_ASS_TYPE_ID_INVL');
3178: hr_utility.raise_error;
3179: else
3180:
3181: close csr_get_rating_scale_id;
3182: --
3194:
3195: (l_weighting_scale_id is NOT NULL AND
3196: p_weighting_level_id is NULL)) then
3197: --
3198: hr_utility.set_location(l_proc,20);
3199: hr_utility.set_message(801,'HR_51646_CEL_RATE_WEG_INVL');
3200: hr_utility.raise_error;
3201: end if;
3202: --
3195: (l_weighting_scale_id is NOT NULL AND
3196: p_weighting_level_id is NULL)) then
3197: --
3198: hr_utility.set_location(l_proc,20);
3199: hr_utility.set_message(801,'HR_51646_CEL_RATE_WEG_INVL');
3200: hr_utility.raise_error;
3201: end if;
3202: --
3203: end if;
3196: p_weighting_level_id is NULL)) then
3197: --
3198: hr_utility.set_location(l_proc,20);
3199: hr_utility.set_message(801,'HR_51646_CEL_RATE_WEG_INVL');
3200: hr_utility.raise_error;
3201: end if;
3202: --
3203: end if;
3204: end if;
3202: --
3203: end if;
3204: end if;
3205: end if;
3206: hr_utility.set_location('Leaving: ' || l_proc, 30);
3207: end chk_rating_weighting_ass_type;
3208:
3209: --
3210: ********/
3387: l_api_updating boolean;
3388: l_exists varchar2(1);
3389: --
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: --
3411: ,p_argument_value => p_business_group_id
3412: );
3413: -- if p_object_id is not null or p_object_name is not null then
3414: if p_object_name is not null and p_object_id is null then
3415: hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
3416: hr_utility.set_message_token('API_NAME', l_proc);
3417: hr_utility.set_message_token('ARGUMENT', 'object_id, object_name');
3418: end if;
3419: end if;
3412: );
3413: -- if p_object_id is not null or p_object_name is not null then
3414: if p_object_name is not null and p_object_id is null then
3415: hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
3416: hr_utility.set_message_token('API_NAME', l_proc);
3417: hr_utility.set_message_token('ARGUMENT', 'object_id, object_name');
3418: end if;
3419: end if;
3420: --
3413: -- if p_object_id is not null or p_object_name is not null then
3414: if p_object_name is not null and p_object_id is null then
3415: hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
3416: hr_utility.set_message_token('API_NAME', l_proc);
3417: hr_utility.set_message_token('ARGUMENT', 'object_id, object_name');
3418: end if;
3419: end if;
3420: --
3421: --
3472: nvl(p_line_score,hr_api.g_number) OR
3473: nvl(per_cel_shd.g_old_rec.qualification_type_id,hr_api.g_number) <>
3474: nvl(p_qualification_type_id,hr_api.g_number))
3475: OR NOT l_api_updating) then
3476: hr_utility.set_location(l_proc, 6);
3477: --
3478: -- Check the parameters status when the type is 'REQUIREMENT'
3479: --
3480: if p_type = 'REQUIREMENT' then
3484: if (p_organization_id is NULL AND
3485: p_job_id is null AND p_position_id IS NULL AND
3486: p_enterprise_id is null) then
3487: --
3488: hr_utility.set_location(l_proc, 7);
3489: hr_utility.set_message(801,'HR_51655_CEL_ORG_JOB_POS');
3490:
3491: hr_utility.raise_error;
3492: --
3485: p_job_id is null AND p_position_id IS NULL AND
3486: p_enterprise_id is null) then
3487: --
3488: hr_utility.set_location(l_proc, 7);
3489: hr_utility.set_message(801,'HR_51655_CEL_ORG_JOB_POS');
3490:
3491: hr_utility.raise_error;
3492: --
3493: elsif ((p_organization_id is not null OR p_enterprise_id is not NULL)
3487: --
3488: hr_utility.set_location(l_proc, 7);
3489: hr_utility.set_message(801,'HR_51655_CEL_ORG_JOB_POS');
3490:
3491: hr_utility.raise_error;
3492: --
3493: elsif ((p_organization_id is not null OR p_enterprise_id is not NULL)
3494: AND (p_valid_grade_id is not null) ) then
3495: hr_utility.set_location(l_proc, 8);
3491: hr_utility.raise_error;
3492: --
3493: elsif ((p_organization_id is not null OR p_enterprise_id is not NULL)
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:
3492: --
3493: elsif ((p_organization_id is not null OR p_enterprise_id is not NULL)
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;
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
3503: is not null OR p_job_id is not null OR p_position_id is not null))
3504: OR
3510: OR
3511: (p_position_id is not null AND(p_enterprise_id is not null OR
3512: p_job_id is not null OR p_organization_id is not null))) then
3513: --
3514: hr_utility.set_location(l_proc, 10);
3515: hr_utility.set_message(801,'HR_51656_CEL_ORG_JOB_POS_ENT');
3516: hr_utility.raise_error;
3517: --
3518: elsif(p_assessment_id is not null
3511: (p_position_id is not null AND(p_enterprise_id is not null OR
3512: p_job_id is not null OR p_organization_id is not null))) then
3513: --
3514: hr_utility.set_location(l_proc, 10);
3515: hr_utility.set_message(801,'HR_51656_CEL_ORG_JOB_POS_ENT');
3516: hr_utility.raise_error;
3517: --
3518: elsif(p_assessment_id is not null
3519: OR p_assessment_type_id is not null
3512: p_job_id is not null OR p_organization_id is not null))) then
3513: --
3514: hr_utility.set_location(l_proc, 10);
3515: hr_utility.set_message(801,'HR_51656_CEL_ORG_JOB_POS_ENT');
3516: hr_utility.raise_error;
3517: --
3518: elsif(p_assessment_id is not null
3519: OR p_assessment_type_id is not null
3520: OR p_activity_version_id is not null
3536: OR p_competence_id is NULL
3537: OR p_mandatory is null)
3538: then
3539: --
3540: hr_utility.set_location(l_proc, 15);
3541: hr_utility.set_message(801,'HR_51657_CEL_REQ_TYPE_ERROR');
3542: hr_utility.raise_error;
3543: --
3544: end if;
3537: OR p_mandatory is null)
3538: then
3539: --
3540: hr_utility.set_location(l_proc, 15);
3541: hr_utility.set_message(801,'HR_51657_CEL_REQ_TYPE_ERROR');
3542: hr_utility.raise_error;
3543: --
3544: end if;
3545: end if;
3538: then
3539: --
3540: hr_utility.set_location(l_proc, 15);
3541: hr_utility.set_message(801,'HR_51657_CEL_REQ_TYPE_ERROR');
3542: hr_utility.raise_error;
3543: --
3544: end if;
3545: end if;
3546: --
3570: OR p_next_certification_date is not null
3571: )
3572: then
3573: --
3574: hr_utility.set_location(l_proc, 20);
3575: hr_utility.set_message(801,'HR_51658_CEL_ASS_TYPE_ERROR');
3576: hr_utility.raise_error;
3577: --
3578: end if;
3571: )
3572: then
3573: --
3574: hr_utility.set_location(l_proc, 20);
3575: hr_utility.set_message(801,'HR_51658_CEL_ASS_TYPE_ERROR');
3576: hr_utility.raise_error;
3577: --
3578: end if;
3579: --
3572: then
3573: --
3574: hr_utility.set_location(l_proc, 20);
3575: hr_utility.set_message(801,'HR_51658_CEL_ASS_TYPE_ERROR');
3576: hr_utility.raise_error;
3577: --
3578: end if;
3579: --
3580: elsif (p_type = 'ASSESSMENT_GROUP') then
3606: OR p_rating_level_id is not null
3607: )
3608: then
3609: --
3610: hr_utility.set_location(l_proc, 25);
3611: hr_utility.set_message(801,'HR_51659_CEL_ASS_GRP_ERROR');
3612: hr_utility.raise_error;
3613: --
3614: end if;
3607: )
3608: then
3609: --
3610: hr_utility.set_location(l_proc, 25);
3611: hr_utility.set_message(801,'HR_51659_CEL_ASS_GRP_ERROR');
3612: hr_utility.raise_error;
3613: --
3614: end if;
3615: elsif (p_type = 'ASSESSMENT_COMPETENCE') then
3608: then
3609: --
3610: hr_utility.set_location(l_proc, 25);
3611: hr_utility.set_message(801,'HR_51659_CEL_ASS_GRP_ERROR');
3612: hr_utility.raise_error;
3613: --
3614: end if;
3615: elsif (p_type = 'ASSESSMENT_COMPETENCE') then
3616: if(p_parent_competence_element_id IS NOT NULL) then
3622: -- raise an error message because the
3623: -- parent_competence_element type is not
3624: -- 'ASSESSMENT_GROUP'
3625: --
3626: hr_utility.set_location(l_proc, 30);
3627: hr_utility.set_message(801,'HR_51660_CEL_ASS_COMP_ERROR');
3628: hr_utility.raise_error;
3629: end if;
3630: close csr_parent_comp_element;
3623: -- parent_competence_element type is not
3624: -- 'ASSESSMENT_GROUP'
3625: --
3626: hr_utility.set_location(l_proc, 30);
3627: hr_utility.set_message(801,'HR_51660_CEL_ASS_COMP_ERROR');
3628: hr_utility.raise_error;
3629: end if;
3630: close csr_parent_comp_element;
3631: end if;
3624: -- 'ASSESSMENT_GROUP'
3625: --
3626: hr_utility.set_location(l_proc, 30);
3627: hr_utility.set_message(801,'HR_51660_CEL_ASS_COMP_ERROR');
3628: hr_utility.raise_error;
3629: end if;
3630: close csr_parent_comp_element;
3631: end if;
3632: if ((p_assessment_type_id is null AND p_parent_competence_element_id
3635:
3636: (p_assessment_type_id is not null AND
3637: p_parent_competence_element_id is not null )) then
3638: --
3639: hr_utility.set_location(l_proc, 35);
3640: hr_utility.set_message(801,'HR_51662_CEL_ASS_COMP_MUTA');
3641: hr_utility.raise_error;
3642: --
3643: end if;
3636: (p_assessment_type_id is not null AND
3637: p_parent_competence_element_id is not null )) then
3638: --
3639: hr_utility.set_location(l_proc, 35);
3640: hr_utility.set_message(801,'HR_51662_CEL_ASS_COMP_MUTA');
3641: hr_utility.raise_error;
3642: --
3643: end if;
3644: if(p_assessment_id is not null
3637: p_parent_competence_element_id is not null )) then
3638: --
3639: hr_utility.set_location(l_proc, 35);
3640: hr_utility.set_message(801,'HR_51662_CEL_ASS_COMP_MUTA');
3641: hr_utility.raise_error;
3642: --
3643: end if;
3644: if(p_assessment_id is not null
3645: OR p_competence_id is NULL
3667: OR p_rating_level_id is not null
3668: )
3669: then
3670: --
3671: hr_utility.set_location(l_proc, 40);
3672: hr_utility.set_message(801,'HR_51663_CEL_ASS_COMP_ERR');
3673: hr_utility.raise_error;
3674:
3675: --
3668: )
3669: then
3670: --
3671: hr_utility.set_location(l_proc, 40);
3672: hr_utility.set_message(801,'HR_51663_CEL_ASS_COMP_ERR');
3673: hr_utility.raise_error;
3674:
3675: --
3676: end if;
3669: then
3670: --
3671: hr_utility.set_location(l_proc, 40);
3672: hr_utility.set_message(801,'HR_51663_CEL_ASS_COMP_ERR');
3673: hr_utility.raise_error;
3674:
3675: --
3676: end if;
3677: --
3705: OR p_rating_level_id is not null
3706: )
3707: then
3708: --
3709: hr_utility.set_location(l_proc, 45);
3710: hr_utility.set_message(801,'HR_51664_CEL_COMP_USG_ERR');
3711: hr_utility.raise_error;
3712: --
3713: end if;
3706: )
3707: then
3708: --
3709: hr_utility.set_location(l_proc, 45);
3710: hr_utility.set_message(801,'HR_51664_CEL_COMP_USG_ERR');
3711: hr_utility.raise_error;
3712: --
3713: end if;
3714:
3707: then
3708: --
3709: hr_utility.set_location(l_proc, 45);
3710: hr_utility.set_message(801,'HR_51664_CEL_COMP_USG_ERR');
3711: hr_utility.raise_error;
3712: --
3713: end if;
3714:
3715: --
3716: elsif (p_type = 'DELIVERY') then
3717: if (p_activity_version_id is NULL)
3718: then
3719: --
3720: hr_utility.set_location(l_proc, 50);
3721: hr_utility.set_message(801,'HR_51665_CEL_DELVR_ERROR');
3722: hr_utility.raise_error;
3723: --
3724: elsif(p_assessment_id is not null
3717: if (p_activity_version_id is NULL)
3718: then
3719: --
3720: hr_utility.set_location(l_proc, 50);
3721: hr_utility.set_message(801,'HR_51665_CEL_DELVR_ERROR');
3722: hr_utility.raise_error;
3723: --
3724: elsif(p_assessment_id is not null
3725: OR p_assessment_type_id is not null
3718: then
3719: --
3720: hr_utility.set_location(l_proc, 50);
3721: hr_utility.set_message(801,'HR_51665_CEL_DELVR_ERROR');
3722: hr_utility.raise_error;
3723: --
3724: elsif(p_assessment_id is not null
3725: OR p_assessment_type_id is not null
3726: OR p_organization_id is not null
3747: OR p_effective_date_from is NULL
3748: )
3749: then
3750: --
3751: hr_utility.set_location(l_proc, 55);
3752: hr_utility.set_message(801,'HR_51666_CEL_DELVR_ERROR');
3753:
3754: hr_utility.raise_error;
3755: --
3748: )
3749: then
3750: --
3751: hr_utility.set_location(l_proc, 55);
3752: hr_utility.set_message(801,'HR_51666_CEL_DELVR_ERROR');
3753:
3754: hr_utility.raise_error;
3755: --
3756: end if;
3750: --
3751: hr_utility.set_location(l_proc, 55);
3752: hr_utility.set_message(801,'HR_51666_CEL_DELVR_ERROR');
3753:
3754: hr_utility.raise_error;
3755: --
3756: end if;
3757: --
3758: -- Note: The PREREQUISITE type is the same as the DELIVERY
3762: elsif (p_type = 'PREREQUISITE') then
3763: if ( p_activity_version_id is NULL)
3764: then
3765: --
3766: hr_utility.set_location(l_proc, 60);
3767:
3768: hr_utility.set_message(801,'HR_51667_CEL_PRE_REQ_MUTA');
3769: hr_utility.raise_error;
3770: --
3764: then
3765: --
3766: hr_utility.set_location(l_proc, 60);
3767:
3768: hr_utility.set_message(801,'HR_51667_CEL_PRE_REQ_MUTA');
3769: hr_utility.raise_error;
3770: --
3771: elsif(p_assessment_id is not null
3772: OR p_assessment_type_id is not null
3765: --
3766: hr_utility.set_location(l_proc, 60);
3767:
3768: hr_utility.set_message(801,'HR_51667_CEL_PRE_REQ_MUTA');
3769: hr_utility.raise_error;
3770: --
3771: elsif(p_assessment_id is not null
3772: OR p_assessment_type_id is not null
3773: OR p_organization_id is not null
3794: OR p_mandatory is NULL
3795: )
3796: then
3797: --
3798: hr_utility.set_location(l_proc, 65);
3799: hr_utility.set_message(801,'HR_51668_CEL_PRE_REQ_ERR');
3800: hr_utility.raise_error;
3801: --
3802: end if;
3795: )
3796: then
3797: --
3798: hr_utility.set_location(l_proc, 65);
3799: hr_utility.set_message(801,'HR_51668_CEL_PRE_REQ_ERR');
3800: hr_utility.raise_error;
3801: --
3802: end if;
3803: --
3796: then
3797: --
3798: hr_utility.set_location(l_proc, 65);
3799: hr_utility.set_message(801,'HR_51668_CEL_PRE_REQ_ERR');
3800: hr_utility.raise_error;
3801: --
3802: end if;
3803: --
3804: elsif (p_type = 'PATH') then
3829: )
3830:
3831: then
3832: --
3833: hr_utility.set_location(l_proc, 70);
3834: hr_utility.set_message(801,'HR_51669_CEL_PATH_ERROR');
3835: hr_utility.raise_error;
3836: --
3837: end if;
3830:
3831: then
3832: --
3833: hr_utility.set_location(l_proc, 70);
3834: hr_utility.set_message(801,'HR_51669_CEL_PATH_ERROR');
3835: hr_utility.raise_error;
3836: --
3837: end if;
3838: --
3831: then
3832: --
3833: hr_utility.set_location(l_proc, 70);
3834: hr_utility.set_message(801,'HR_51669_CEL_PATH_ERROR');
3835: hr_utility.raise_error;
3836: --
3837: end if;
3838: --
3839: elsif (p_type = 'PERSONAL') then
3861: OR p_competence_type is not null
3862: )
3863: then
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;
3862: )
3863: then
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: --
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: --
3873: elsif (p_type = 'PROPOSAL') then
3896:
3897: )
3898: then
3899: --
3900: hr_utility.set_location(l_proc, 85);
3901: hr_utility.set_message(801,'HR_51672_CEL_PRO_TYPE_ERROR');
3902: hr_utility.raise_error;
3903: --
3904: end if;
3897: )
3898: then
3899: --
3900: hr_utility.set_location(l_proc, 85);
3901: hr_utility.set_message(801,'HR_51672_CEL_PRO_TYPE_ERROR');
3902: hr_utility.raise_error;
3903: --
3904: end if;
3905: --
3898: then
3899: --
3900: hr_utility.set_location(l_proc, 85);
3901: hr_utility.set_message(801,'HR_51672_CEL_PRO_TYPE_ERROR');
3902: hr_utility.raise_error;
3903: --
3904: end if;
3905: --
3906: elsif (p_type = 'SET') then
3933: )
3934: then
3935:
3936: --
3937: hr_utility.set_location(l_proc, 90);
3938: hr_utility.set_message(801,'HR_51673_CEL_SET_TYPE_ERROR');
3939: hr_utility.raise_error;
3940: --
3941: end if;
3934: then
3935:
3936: --
3937: hr_utility.set_location(l_proc, 90);
3938: hr_utility.set_message(801,'HR_51673_CEL_SET_TYPE_ERROR');
3939: hr_utility.raise_error;
3940: --
3941: end if;
3942: elsif (p_type = 'QUALIFICATION') then
3935:
3936: --
3937: hr_utility.set_location(l_proc, 90);
3938: hr_utility.set_message(801,'HR_51673_CEL_SET_TYPE_ERROR');
3939: hr_utility.raise_error;
3940: --
3941: end if;
3942: elsif (p_type = 'QUALIFICATION') then
3943: hr_utility.trace('date_from : ' || p_effective_date_from);
3939: hr_utility.raise_error;
3940: --
3941: end if;
3942: elsif (p_type = 'QUALIFICATION') then
3943: hr_utility.trace('date_from : ' || p_effective_date_from);
3944: hr_utility.trace('date_to : ' || p_effective_date_to);
3945: hr_utility.set_location(l_proc, 100);
3946:
3947: --
3940: --
3941: end if;
3942: elsif (p_type = 'QUALIFICATION') then
3943: hr_utility.trace('date_from : ' || p_effective_date_from);
3944: hr_utility.trace('date_to : ' || p_effective_date_to);
3945: hr_utility.set_location(l_proc, 100);
3946:
3947: --
3948: -- Mandatory parameter check
3941: end if;
3942: elsif (p_type = 'QUALIFICATION') then
3943: hr_utility.trace('date_from : ' || p_effective_date_from);
3944: hr_utility.trace('date_to : ' || p_effective_date_to);
3945: hr_utility.set_location(l_proc, 100);
3946:
3947: --
3948: -- Mandatory parameter check
3949: --
3957: p_argument => 'effective_date_from',
3958: p_argument_value => p_effective_date_from);
3959: --
3960: if (NOT l_api_updating) then
3961: hr_utility.set_location(l_proc, 103);
3962: --
3963: -- Mandatory parameter check
3964: --
3965: hr_api.mandatory_arg_error
3970: open csr_comp_qual_link;
3971: fetch csr_comp_qual_link into l_exists;
3972: if csr_comp_qual_link%FOUND then
3973: close csr_comp_qual_link;
3974: hr_utility.set_message(800,'HR_449136_QUA_FWK_LINK_EXISTS');
3975: hr_utility.raise_error;
3976: end if;
3977: close csr_comp_qual_link;
3978: else
3971: fetch csr_comp_qual_link into l_exists;
3972: if csr_comp_qual_link%FOUND then
3973: close csr_comp_qual_link;
3974: hr_utility.set_message(800,'HR_449136_QUA_FWK_LINK_EXISTS');
3975: hr_utility.raise_error;
3976: end if;
3977: close csr_comp_qual_link;
3978: else
3979: hr_utility.set_location(l_proc, 105);
3975: hr_utility.raise_error;
3976: end if;
3977: close csr_comp_qual_link;
3978: else
3979: hr_utility.set_location(l_proc, 105);
3980:
3981: open csr_upd_comp_qual_link;
3982: fetch csr_upd_comp_qual_link into l_exists;
3983: if csr_upd_comp_qual_link%FOUND then
3981: open csr_upd_comp_qual_link;
3982: fetch csr_upd_comp_qual_link into l_exists;
3983: if csr_upd_comp_qual_link%FOUND then
3984: close csr_upd_comp_qual_link;
3985: hr_utility.set_message(800,'HR_449136_QUA_FWK_LINK_EXISTS');
3986: hr_utility.raise_error;
3987: end if;
3988: close csr_upd_comp_qual_link;
3989: end if;
3982: fetch csr_upd_comp_qual_link into l_exists;
3983: if csr_upd_comp_qual_link%FOUND then
3984: close csr_upd_comp_qual_link;
3985: hr_utility.set_message(800,'HR_449136_QUA_FWK_LINK_EXISTS');
3986: hr_utility.raise_error;
3987: end if;
3988: close csr_upd_comp_qual_link;
3989: end if;
3990: end if;
3987: end if;
3988: close csr_upd_comp_qual_link;
3989: end if;
3990: end if;
3991: hr_utility.set_location('Leaving: ' || l_proc, 110);
3992: end if;
3993: end if; -- check for no_exclusive_error for TYPE.
3994: exception
3995: when app_exception.application_exception then
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;
4002: hr_utility.set_location(' Leaving:'||l_proc,120);
4003: end chk_type_and_validation;
3998: ) then
3999: hr_utility.set_location(' Leaving:'||l_proc,110);
4000: raise;
4001: end if;
4002: hr_utility.set_location(' Leaving:'||l_proc,120);
4003: end chk_type_and_validation;
4004: --
4005: --
4006: --
4094: l_api_updating boolean;
4095: l_exists varchar2(1);
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.
4134: nvl(p_competence_type,hr_api.g_varchar2) OR
4135: nvl(per_cel_shd.g_old_rec.effective_date_from,hr_api.g_date) <>
4136: nvl(p_effective_date_from,hr_api.g_date))
4137: OR NOT l_api_updating) then
4138: hr_utility.set_location(l_proc, 6);
4139: --
4140: -- build the NATIVE dynamic SQL
4141: -- note: native dynamic SQL has been used for performance
4142: l_sql_stmt := 'SELECT NULL '||
4275: ELSE
4276: l_sql_stmt := l_sql_stmt||
4277: 'AND :p_qualification_type_id IS NULL /* qualification_type_id IS NULL */ ';
4278: END IF;
4279: hr_utility.set_location(l_proc,10);
4280: --hr_utility.trace('l_sql_stmt : ' || l_sql_stmt);
4281: -- dynamically execute the SQL
4282: BEGIN
4283: EXECUTE IMMEDIATE l_sql_stmt
4276: l_sql_stmt := l_sql_stmt||
4277: 'AND :p_qualification_type_id IS NULL /* qualification_type_id IS NULL */ ';
4278: END IF;
4279: hr_utility.set_location(l_proc,10);
4280: --hr_utility.trace('l_sql_stmt : ' || l_sql_stmt);
4281: -- dynamically execute the SQL
4282: BEGIN
4283: EXECUTE IMMEDIATE l_sql_stmt
4284: INTO l_exists
4303: p_party_id, -- HR/TCA merge
4304: p_qualification_type_id;
4305: -- executed successful therefore a row has been found
4306: if p_type = 'COMPETENCE_USAGE' then
4307: hr_utility.set_message(800,'HR_52262_CEL_UNIQUE_COMP_USAGE');
4308: elsif p_type = 'ASSESSMENT' then
4309: hr_utility.set_message(800,'HR_52263_CEL_UNIQUE_ASSESSMENT');
4310: elsif p_type = 'ASSESSMNET_COMPETENCE' then
4311: hr_utility.set_message(800,'HR_52264_CEL_UNIQUE_ASM_COMP');
4305: -- executed successful therefore a row has been found
4306: if p_type = 'COMPETENCE_USAGE' then
4307: hr_utility.set_message(800,'HR_52262_CEL_UNIQUE_COMP_USAGE');
4308: elsif p_type = 'ASSESSMENT' then
4309: hr_utility.set_message(800,'HR_52263_CEL_UNIQUE_ASSESSMENT');
4310: elsif p_type = 'ASSESSMNET_COMPETENCE' then
4311: hr_utility.set_message(800,'HR_52264_CEL_UNIQUE_ASM_COMP');
4312: elsif p_type = 'ASSESSMENT_GROUP' then
4313: hr_utility.set_message(800,'HR_52265_CEL_UNIQUE_ASM_GROUP');
4307: hr_utility.set_message(800,'HR_52262_CEL_UNIQUE_COMP_USAGE');
4308: elsif p_type = 'ASSESSMENT' then
4309: hr_utility.set_message(800,'HR_52263_CEL_UNIQUE_ASSESSMENT');
4310: elsif p_type = 'ASSESSMNET_COMPETENCE' then
4311: hr_utility.set_message(800,'HR_52264_CEL_UNIQUE_ASM_COMP');
4312: elsif p_type = 'ASSESSMENT_GROUP' then
4313: hr_utility.set_message(800,'HR_52265_CEL_UNIQUE_ASM_GROUP');
4314: elsif p_type = 'REQUIREMENT' then
4315: hr_utility.set_message(800,'HR_52266_CEL_UNIQUE_REQUIREMEN');
4309: hr_utility.set_message(800,'HR_52263_CEL_UNIQUE_ASSESSMENT');
4310: elsif p_type = 'ASSESSMNET_COMPETENCE' then
4311: hr_utility.set_message(800,'HR_52264_CEL_UNIQUE_ASM_COMP');
4312: elsif p_type = 'ASSESSMENT_GROUP' then
4313: hr_utility.set_message(800,'HR_52265_CEL_UNIQUE_ASM_GROUP');
4314: elsif p_type = 'REQUIREMENT' then
4315: hr_utility.set_message(800,'HR_52266_CEL_UNIQUE_REQUIREMEN');
4316: elsif p_type = 'DELIVERY' then
4317: hr_utility.set_message(800,'HR_52267_CEL_UNIQUE_DELIVERY');
4311: hr_utility.set_message(800,'HR_52264_CEL_UNIQUE_ASM_COMP');
4312: elsif p_type = 'ASSESSMENT_GROUP' then
4313: hr_utility.set_message(800,'HR_52265_CEL_UNIQUE_ASM_GROUP');
4314: elsif p_type = 'REQUIREMENT' then
4315: hr_utility.set_message(800,'HR_52266_CEL_UNIQUE_REQUIREMEN');
4316: elsif p_type = 'DELIVERY' then
4317: hr_utility.set_message(800,'HR_52267_CEL_UNIQUE_DELIVERY');
4318: elsif p_type = 'PERSONAL' then
4319: hr_utility.set_message(800,'HR_52268_CEL_UNIQUE_PERSONAL');
4313: hr_utility.set_message(800,'HR_52265_CEL_UNIQUE_ASM_GROUP');
4314: elsif p_type = 'REQUIREMENT' then
4315: hr_utility.set_message(800,'HR_52266_CEL_UNIQUE_REQUIREMEN');
4316: elsif p_type = 'DELIVERY' then
4317: hr_utility.set_message(800,'HR_52267_CEL_UNIQUE_DELIVERY');
4318: elsif p_type = 'PERSONAL' then
4319: hr_utility.set_message(800,'HR_52268_CEL_UNIQUE_PERSONAL');
4320: else
4321: hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4315: hr_utility.set_message(800,'HR_52266_CEL_UNIQUE_REQUIREMEN');
4316: elsif p_type = 'DELIVERY' then
4317: hr_utility.set_message(800,'HR_52267_CEL_UNIQUE_DELIVERY');
4318: elsif p_type = 'PERSONAL' then
4319: hr_utility.set_message(800,'HR_52268_CEL_UNIQUE_PERSONAL');
4320: else
4321: hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4322: end if;
4323: --
4317: hr_utility.set_message(800,'HR_52267_CEL_UNIQUE_DELIVERY');
4318: elsif p_type = 'PERSONAL' then
4319: hr_utility.set_message(800,'HR_52268_CEL_UNIQUE_PERSONAL');
4320: else
4321: hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4322: end if;
4323: --
4324: hr_utility.raise_error;
4325: EXCEPTION
4320: else
4321: hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4322: end if;
4323: --
4324: hr_utility.raise_error;
4325: EXCEPTION
4326: WHEN NO_DATA_FOUND THEN
4327: null;
4328: END;
4326: WHEN NO_DATA_FOUND THEN
4327: null;
4328: END;
4329: end if;
4330: hr_utility.set_location(l_proc,7);
4331: --
4332: end if; -- no_exclusive_check for TYPE
4333: hr_utility.set_location('Leaving: ' || l_proc, 10);
4334: exception
4329: end if;
4330: hr_utility.set_location(l_proc,7);
4331: --
4332: end if; -- no_exclusive_check for TYPE
4333: hr_utility.set_location('Leaving: ' || l_proc, 10);
4334: exception
4335: when app_exception.application_exception then
4336: hr_multi_message.add;
4337: hr_utility.set_location(' Leaving:'||l_proc,105);
4333: hr_utility.set_location('Leaving: ' || l_proc, 10);
4334: exception
4335: when app_exception.application_exception then
4336: hr_multi_message.add;
4337: hr_utility.set_location(' Leaving:'||l_proc,105);
4338: end chk_unique_competence_element;
4339: /*
4340: --
4341: -- ----------------------------------------------------------------------------
4384: and p_effective_date between effective_date_from
4385: and nvl(effective_date_to,hr_api.g_eot);
4386: Begin
4387: --
4388: hr_utility.set_location('Entering:'||l_proc,10);
4389: if p_qualification_type_id is not NULL and p_competence_id is not NULL
4390: then
4391: --
4392: -- Only proceed with validation if :
4402: hr_api.g_number) = nvl(p_competence_id, hr_api.g_number)
4403: and nvl(per_cel_shd.g_old_rec.qualification_type_id,hr_api.g_number)
4404: = nvl(p_qualification_type_id, hr_api.g_number)
4405: ) then
4406: hr_utility.set_location('Leaving.... ' || l_proc,20);
4407: return;
4408: end if;
4409:
4410: hr_utility.set_location(l_proc,20);
4406: hr_utility.set_location('Leaving.... ' || l_proc,20);
4407: return;
4408: end if;
4409:
4410: hr_utility.set_location(l_proc,20);
4411:
4412: open csr_unique_comp_qual;
4413: fetch csr_unique_comp_qual into l_exists;
4414: if csr_unique_comp_qual%found then
4413: fetch csr_unique_comp_qual into l_exists;
4414: if csr_unique_comp_qual%found then
4415: close csr_unique_comp_qual;
4416: --
4417: hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4418: hr_utility.raise_error;
4419: --
4420: end if;
4421: close csr_unique_comp_qual;
4414: if csr_unique_comp_qual%found then
4415: close csr_unique_comp_qual;
4416: --
4417: hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4418: hr_utility.raise_error;
4419: --
4420: end if;
4421: close csr_unique_comp_qual;
4422: end if;
4420: end if;
4421: close csr_unique_comp_qual;
4422: end if;
4423:
4424: hr_utility.set_location('Leaving:'||l_proc,30);
4425: --
4426: End chk_unique_comp_qual;
4427: */
4428: --
4554:
4555: --
4556: begin
4557: --
4558: hr_utility.set_location('Entering:'|| l_proc, 1);
4559: --
4560: -- Only do the delete validation if the type is
4561: -- ASSESSESSMENT_GROUP, ASSESSMENT_COMPETENCE, COMPETENCE_USAGE or
4562: -- or PERSONAL,ASSESSMENT.
4570: -- raise an error message if the type = 'PERSONAL'
4571: --
4572: -- commented out following section due to bug raised (no. 525537) 1-09-97
4573: -- if p_type = 'PERSONAL' then
4574: -- hr_utility.set_location(l_proc,5);
4575: -- hr_utility.set_message(801,'HR_51675_CEL_PER_CANT_DEL');
4576: -- hr_utility.raise_error;
4577: -- end if;
4578: --
4571: --
4572: -- commented out following section due to bug raised (no. 525537) 1-09-97
4573: -- if p_type = 'PERSONAL' then
4574: -- hr_utility.set_location(l_proc,5);
4575: -- hr_utility.set_message(801,'HR_51675_CEL_PER_CANT_DEL');
4576: -- hr_utility.raise_error;
4577: -- end if;
4578: --
4579: --
4572: -- commented out following section due to bug raised (no. 525537) 1-09-97
4573: -- if p_type = 'PERSONAL' then
4574: -- hr_utility.set_location(l_proc,5);
4575: -- hr_utility.set_message(801,'HR_51675_CEL_PER_CANT_DEL');
4576: -- hr_utility.raise_error;
4577: -- end if;
4578: --
4579: --
4580: if(p_type = 'ASSESSMENT_GROUP') then
4585: open csr_is_parent_comp;
4586: fetch csr_is_parent_comp into l_exists;
4587: if csr_is_parent_comp%found then
4588: close csr_is_parent_comp;
4589: hr_utility.set_location(l_proc,15);
4590: hr_utility.set_message(801,'HR_51677_CEL_PARNT_CANT_DEL');
4591: hr_utility.raise_error;
4592:
4593: end if;
4586: fetch csr_is_parent_comp into l_exists;
4587: if csr_is_parent_comp%found then
4588: close csr_is_parent_comp;
4589: hr_utility.set_location(l_proc,15);
4590: hr_utility.set_message(801,'HR_51677_CEL_PARNT_CANT_DEL');
4591: hr_utility.raise_error;
4592:
4593: end if;
4594: close csr_is_parent_comp;
4587: if csr_is_parent_comp%found then
4588: close csr_is_parent_comp;
4589: hr_utility.set_location(l_proc,15);
4590: hr_utility.set_message(801,'HR_51677_CEL_PARNT_CANT_DEL');
4591: hr_utility.raise_error;
4592:
4593: end if;
4594: close csr_is_parent_comp;
4595: end if;
4604: fetch csr_get_comp_group into l_exists;
4605: if csr_get_comp_group%found then
4606:
4607: close csr_get_comp_group;
4608: hr_utility.set_location(l_proc,20);
4609: hr_utility.set_message(801,'HR_51678_CEL_COM_USG_CANT_DEL');
4610: hr_utility.raise_error;
4611: end if;
4612: close csr_get_comp_group;
4605: if csr_get_comp_group%found then
4606:
4607: close csr_get_comp_group;
4608: hr_utility.set_location(l_proc,20);
4609: hr_utility.set_message(801,'HR_51678_CEL_COM_USG_CANT_DEL');
4610: hr_utility.raise_error;
4611: end if;
4612: close csr_get_comp_group;
4613: end if;
4606:
4607: close csr_get_comp_group;
4608: hr_utility.set_location(l_proc,20);
4609: hr_utility.set_message(801,'HR_51678_CEL_COM_USG_CANT_DEL');
4610: hr_utility.raise_error;
4611: end if;
4612: close csr_get_comp_group;
4613: end if;
4614: --
4621: open csr_get_used_comp_element;
4622: fetch csr_get_used_comp_element into l_exists;
4623: if csr_get_used_comp_element%found then
4624: close csr_get_used_comp_element;
4625: hr_utility.set_location(l_proc,25);
4626: hr_utility.set_message(801,'HR_51679_CEL_ASS_COMP_CANT_DEL');
4627: hr_utility.raise_error;
4628: end if;
4629: --
4622: fetch csr_get_used_comp_element into l_exists;
4623: if csr_get_used_comp_element%found then
4624: close csr_get_used_comp_element;
4625: hr_utility.set_location(l_proc,25);
4626: hr_utility.set_message(801,'HR_51679_CEL_ASS_COMP_CANT_DEL');
4627: hr_utility.raise_error;
4628: end if;
4629: --
4630: close csr_get_used_comp_element;
4623: if csr_get_used_comp_element%found then
4624: close csr_get_used_comp_element;
4625: hr_utility.set_location(l_proc,25);
4626: hr_utility.set_message(801,'HR_51679_CEL_ASS_COMP_CANT_DEL');
4627: hr_utility.raise_error;
4628: end if;
4629: --
4630: close csr_get_used_comp_element;
4631: end if;
4631: end if;
4632: --
4633: end if;
4634: --
4635: hr_utility.set_location(l_proc,30);
4636:
4637: open csr_comp_element_outcome;
4638: fetch csr_comp_element_outcome into l_exists;
4639: if csr_comp_element_outcome%FOUND then
4637: open csr_comp_element_outcome;
4638: fetch csr_comp_element_outcome into l_exists;
4639: if csr_comp_element_outcome%FOUND then
4640: close csr_comp_element_outcome;
4641: hr_utility.set_message(800,'HR_449135_QUA_FWK_CEL_TAB_REF');
4642: hr_utility.raise_error;
4643: end if;
4644: close csr_comp_element_outcome;
4645:
4638: fetch csr_comp_element_outcome into l_exists;
4639: if csr_comp_element_outcome%FOUND then
4640: close csr_comp_element_outcome;
4641: hr_utility.set_message(800,'HR_449135_QUA_FWK_CEL_TAB_REF');
4642: hr_utility.raise_error;
4643: end if;
4644: close csr_comp_element_outcome;
4645:
4646: hr_utility.set_location('Leaving: ' || l_proc,40);
4642: hr_utility.raise_error;
4643: end if;
4644: close csr_comp_element_outcome;
4645:
4646: hr_utility.set_location('Leaving: ' || l_proc,40);
4647: end chk_comp_element_delete;
4648: -- -----------------------------------------------------------------------
4649: -- |------------------------------< chk_df >-----------------------------|
4650: -- -----------------------------------------------------------------------
4677: --
4678: l_proc varchar2(72) := g_package||'chk_df';
4679: --
4680: begin
4681: hr_utility.set_location('Entering:'||l_proc, 10);
4682: --
4683: if ((p_rec.valid_grade_id is not null) and (
4684: nvl(per_cel_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
4685: nvl(p_rec.attribute_category, hr_api.g_varchar2) or
4777: ,p_attribute20_value => p_rec.attribute20
4778: );
4779: end if;
4780: --
4781: hr_utility.set_location(' Leaving:'||l_proc, 20);
4782:
4783: end chk_df;
4784: --
4785: -- ----------------------------------------------------------------------------
4816: --
4817: l_proc varchar2(72) := g_package || 'chk_ddf';
4818: --
4819: begin
4820: hr_utility.set_location('Entering:'||l_proc,10);
4821: --
4822: if ((p_rec.competence_element_id is not null) and (
4823: nvl(per_cel_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
4824: nvl(p_rec.information_category, hr_api.g_varchar2) or
4913: ,p_attribute20_value => p_rec.information20
4914: );
4915: end if;
4916: --
4917: hr_utility.set_location(' Leaving:'||l_proc,20);
4918: end chk_ddf;
4919: --
4920: -- ----------------------------------------------------------------------------
4921: -- |---------------------------< insert_validate >----------------------------|
4925: --
4926: l_proc varchar2(72) := g_package||'insert_validate';
4927: --
4928: Begin
4929: hr_utility.set_location('Entering:'||l_proc, 5);
4930: --
4931: -- Validate important attributes
4932: --
4933: -- Call all supporting business operations
4972: ,p_type => p_rec.type
4973: ,p_object_version_number => p_rec.object_version_number
4974: );
4975: --
4976: hr_utility.set_location(l_proc, 10);
4977: --
4978: --
4979: -- Business Rule Mapping
4980: -- =====================
5025: ,p_qualification_type_id => p_rec.qualification_type_id
5026: );
5027: --
5028:
5029: hr_utility.set_location(l_proc, 15);
5030: --
5031: -- Business Rule Mapping
5032: -- =====================
5033: -- CHK_UNIQUE_COMPETENCE_ELEMENT
5060: ,p_party_id => p_rec.party_id -- HR/TCA merge
5061: ,p_qualification_type_id => p_rec.qualification_type_id
5062: );
5063: --
5064: hr_utility.set_location(l_proc, 16);
5065: --
5066: -- Business Rule Mapping
5067: -- =====================
5068: -- CHK_CERTIFICATION_METHOD
5073: ,p_certification_method => p_rec.certification_method
5074: ,p_object_version_number => p_rec.object_version_number
5075: );
5076: --
5077: hr_utility.set_location(l_proc, 20);
5078: --
5079: -- Business Rule Mapping
5080: -- =====================
5081: -- CHK_COMPETENCE_TYPE
5086: ,p_competence_type => p_rec.competence_type
5087: ,p_object_version_number => p_rec.object_version_number
5088: );
5089: --
5090: hr_utility.set_location(l_proc, 25);
5091: --
5092: -- Business Rule Mapping
5093: -- =====================
5094: -- CHK_GROUP_COMPETENCE_TYPE
5099: ,p_competence_type => p_rec.group_competence_type
5100: ,p_object_version_number => p_rec.object_version_number
5101: );
5102: --
5103: hr_utility.set_location(l_proc, 30);
5104: --
5105: -- Business Rule Mapping
5106: -- =====================
5107: -- CHK_MANDATORY
5113: ,p_mandatory => p_rec.mandatory
5114: ,p_object_version_number => p_rec.object_version_number
5115: );
5116: --
5117: hr_utility.set_location(l_proc, 35);
5118: --
5119: -- Business Rule Mapping
5120: -- =====================
5121: -- CHK_SOURC_OF_PROFICIENCY_LEVEL
5128: => p_rec.source_of_proficiency_level
5129: ,p_object_version_number => p_rec.object_version_number
5130: );
5131: --
5132: hr_utility.set_location(l_proc, 40);
5133: --
5134: -- Business Rule Mapping
5135: -- =====================
5136: -- CHK_CERTIFICATION_METHOD_DATE
5142: ,p_certification_method => p_rec.certification_method
5143: ,p_object_version_number => p_rec.object_version_number
5144: );
5145: --
5146: hr_utility.set_location(l_proc, 45);
5147: --
5148: -- Business Rule Mapping
5149: -- =====================
5150: -- CHK_NEXT_CERTIFICATION_DATE
5156: ,p_object_version_number => p_rec.object_version_number
5157: ,p_effective_date_from => p_rec.effective_date_from -- added for fix of #731089
5158: );
5159: --
5160: hr_utility.set_location(l_proc, 48);
5161: --
5162: -- Business Rule Mapping
5163: -- =====================
5164: -- CHK_FOREIGN_KEYS
5193: ,p_party_id => p_rec.party_id -- HR/TCA merge
5194: ,p_qualification_type_id => p_rec.qualification_type_id
5195: );
5196: --
5197: hr_utility.set_location('Entering:'||l_proc, 50);
5198:
5199: --
5200: -- Business Rule Mapping
5201: -- =====================
5206: ,p_effective_date
5207: );
5208:
5209: --
5210: hr_utility.set_location('Entering:'||l_proc, 52);
5211:
5212: --
5213: -- Business Rule Mapping
5214: -- =====================
5224: ,p_object_version_number => p_rec.object_version_number
5225: ,p_party_id => p_rec.party_id
5226: );
5227: --
5228: hr_utility.set_location(l_proc, 55);
5229: --
5230: -- Business Rule Mapping
5231: -- =====================
5232: -- CHK_RATING_WEIGHTING_ID
5242: ,p_type => p_rec.type
5243: ,p_party_id => p_rec.party_id
5244: );
5245: --
5246: hr_utility.set_location(l_proc, 60);
5247: --
5248: -- Business Rule Mapping
5249: -- =====================
5250: -- CHK_COMPETENCE_ELEMENT_DATES
5264: ,p_object_version_number => p_rec.object_version_number
5265: ,p_enterprise_id => p_rec.enterprise_id
5266: );
5267: --
5268: hr_utility.set_location(l_proc, 65);
5269:
5270: --
5271: -- Business Rule Mapping
5272: -- =====================
5281: ,p_object_version_number => p_rec.object_version_number
5282: );
5283:
5284: --
5285: hr_utility.set_location(l_proc, 70);
5286:
5287: /*
5288: -- Business Rule Mapping
5289: -- =====================
5297: ,p_effective_date => p_effective_date
5298: );
5299: */
5300: --
5301: hr_utility.set_location(l_proc, 80);
5302: --
5303: -- do the descriptive flex validation.
5304: --
5305: per_cel_bus.chk_df(p_rec => p_rec);
5303: -- do the descriptive flex validation.
5304: --
5305: per_cel_bus.chk_df(p_rec => p_rec);
5306:
5307: hr_utility.set_location(l_proc, 90);
5308:
5309: --
5310: -- do the developer descriptive flex validation.
5311: --
5310: -- do the developer descriptive flex validation.
5311: --
5312: per_cel_bus.chk_ddf(p_rec => p_rec);
5313: --
5314: hr_utility.set_location('Leaving:'||l_proc, 100);
5315: --
5316: End insert_validate;
5317: --
5318: -- ----------------------------------------------------------------------------
5323: --
5324: l_proc varchar2(72) := g_package||'update_validate';
5325: --
5326: Begin
5327: hr_utility.set_location('Entering:'||l_proc, 5);
5328: -- Validate Important Attributes
5329: --
5330: -- Business Rule Mapping
5331: -- =====================
5361: --
5362: per_cel_bus.check_non_updateable_args
5363: (p_rec =>p_rec);
5364: --
5365: hr_utility.set_location (l_proc,6);
5366: --
5367: -- Business Rule Mapping
5368: -- =====================
5369: -- CHK_TYPE_AND_VALIDATION
5413: ,p_party_id => p_rec.party_id -- HR/TCA merge
5414: ,p_qualification_type_id => p_rec.qualification_type_id -- BUG3356369
5415: );
5416: --
5417: hr_utility.set_location(l_proc, 15);
5418:
5419: --
5420: -- Business Rule Mapping
5421: -- =====================
5449: ,p_party_id => p_rec.party_id -- HR/TCA merge
5450: ,p_qualification_type_id => p_rec.qualification_type_id
5451: );
5452: --
5453: hr_utility.set_location(l_proc, 16);
5454: --
5455: -- Business Rule Mapping
5456: -- =====================
5457: -- CHK_CERTIFICATION_METHOD
5463:
5464: ,p_object_version_number => p_rec.object_version_number
5465: );
5466: --
5467: hr_utility.set_location(l_proc, 20);
5468: --
5469: -- Business Rule Mapping
5470: -- =====================
5471: -- CHK_COMPETENCE_TYPE
5477:
5478: ,p_object_version_number => p_rec.object_version_number
5479: );
5480: --
5481: hr_utility.set_location(l_proc, 25);
5482: --
5483: -- Business Rule Mapping
5484: -- =====================
5485: -- CHK_GROUP_COMPETENCE_TYPE
5491:
5492: ,p_object_version_number => p_rec.object_version_number
5493: );
5494: --
5495: hr_utility.set_location(l_proc, 30);
5496: --
5497: -- Business Rule Mapping
5498: -- =====================
5499: -- CHK_MANDATORY
5505:
5506: ,p_object_version_number => p_rec.object_version_number
5507: );
5508: --
5509: hr_utility.set_location(l_proc, 35);
5510: --
5511: -- Business Rule Mapping
5512: -- =====================
5513: -- CHK_SOURC_OF_PROFICIENCY_LEVEL
5520: => p_rec.source_of_proficiency_level
5521: ,p_object_version_number => p_rec.object_version_number
5522: );
5523: --
5524: hr_utility.set_location(l_proc, 40);
5525: --
5526: -- Business Rule Mapping
5527: -- =====================
5528: -- CHK_CERTIFICATION_METHOD_DATE
5534: ,p_certification_method => p_rec.certification_method
5535: ,p_object_version_number => p_rec.object_version_number
5536: );
5537: --
5538: hr_utility.set_location(l_proc, 45);
5539: --
5540: -- Business Rule Mapping
5541: -- =====================
5542: -- CHK_NEXT_CERTIFICATION_DATE
5549: ,p_object_version_number => p_rec.object_version_number
5550: ,p_effective_date_from => p_rec.effective_date_from --added for bug fix of #731089
5551: );
5552: --
5553: hr_utility.set_location(l_proc, 48);
5554:
5555: -- Business Rule Mapping
5556: -- =====================
5557: -- CHK_PROFICIENCY_LEVEL_ID
5567: ,p_object_version_number => p_rec.object_version_number
5568: ,p_party_id => p_rec.party_id
5569: );
5570: --
5571: hr_utility.set_location(l_proc, 55);
5572: --
5573: -- Business Rule Mapping
5574: -- =====================
5575: -- CHK_RATING_WEIGHTING_ID
5585: ,p_type => p_rec.type
5586: ,p_party_id => p_rec.party_id
5587: );
5588: --
5589: hr_utility.set_location(l_proc, 60);
5590: --
5591: -- Business Rule Mapping
5592:
5593: -- =====================
5608: ,p_object_version_number => p_rec.object_version_number
5609: ,p_enterprise_id => p_rec.enterprise_id
5610: );
5611: --
5612: hr_utility.set_location(l_proc, 65);
5613: --
5614: -- Business Rule Mapping
5615: -- =====================
5616: -- CHK_NORMAL_ELAPSE_DURATION
5624: => p_rec.normal_elapse_duration_unit
5625: ,p_object_version_number => p_rec.object_version_number
5626: );
5627: --
5628: hr_utility.set_location(l_proc, 70);
5629: --
5630: --
5631: -- Business Rule Mapping
5632: -- =====================
5663: ,p_qualification_type_id => p_rec.qualification_type_id
5664: );
5665:
5666: --
5667: hr_utility.set_location(l_proc, 70);
5668: /*
5669: -- Business Rule Mapping
5670: -- =====================
5671: -- CHK_UNIQ_COMP_QUAL
5678: ,p_effective_date => p_effective_date
5679: );
5680:
5681: */
5682: hr_utility.set_location(l_proc, 80);
5683:
5684: --
5685: -- do the descriptive flex validation.
5686: --
5685: -- do the descriptive flex validation.
5686: --
5687: per_cel_bus.chk_df(p_rec => p_rec);
5688:
5689: hr_utility.set_location(l_proc, 80);
5690:
5691: --
5692: -- do the developer descriptive flex validation.
5693: --
5692: -- do the developer descriptive flex validation.
5693: --
5694: per_cel_bus.chk_ddf(p_rec => p_rec);
5695:
5696: hr_utility.set_location('Leaving:'||l_proc, 80);
5697: End update_validate;
5698: --
5699: -- ----------------------------------------------------------------------------
5700: -- |---------------------------< delete_validate >----------------------------|
5703: --
5704: l_proc varchar2(72) := g_package||'delete_validate';
5705: --
5706: Begin
5707: hr_utility.set_location('Entering:'||l_proc, 5);
5708: --
5709: -- Call all supporting business operations
5710: -- Business Rule Mapping
5711: -- =====================
5724: ,p_group_competence_type => per_cel_shd.g_old_rec.group_competence_type
5725: );
5726: --
5727:
5728: hr_utility.set_location(' Leaving:'||l_proc, 10);
5729: End delete_validate;
5730: --
5731: -- ----------------------------------------------------------------------------
5732: -- |-----------------------< return_legislation_code >-------------------------|
5748: l_legislation_code varchar2(150);
5749: l_business_group_flag varchar2(1);
5750: --
5751: Begin
5752: hr_utility.set_location('Entering:'||l_proc, 5);
5753: --
5754: -- Ensure that all the mandatory parameters are not null
5755: --
5756: hr_api.mandatory_arg_error (p_api_name => l_proc,
5773: -- call to this function. Just return the value in the global
5774: -- variable.
5775: --
5776: l_legislation_code := g_legislation_code;
5777: hr_utility.set_location(l_proc, 20);
5778: else
5779: --
5780: -- The ID is different to the last call to this function
5781: -- or this is the first call to this function.
5786: close csr_leg_code;
5787: --
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;
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;
5797: g_competence_element_id:= p_competence_element_id;
5798: g_legislation_code := l_legislation_code;
5799: end if;
5800: return l_legislation_code;
5801: --
5802: hr_utility.set_location(' Leaving:'||l_proc, 10);
5803: --
5804: End return_legislation_code;
5805: --
5806: --