[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: --
3410: ,p_argument => 'business_group_id'
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: hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
3415: hr_utility.set_message_token('API_NAME', l_proc);
3416: hr_utility.set_message_token('ARGUMENT', 'object_id, object_name');
3417: end if;
3418: end if;
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: hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
3415: hr_utility.set_message_token('API_NAME', l_proc);
3416: hr_utility.set_message_token('ARGUMENT', 'object_id, object_name');
3417: end if;
3418: end if;
3419: --
3412: );
3413: if p_object_id is not null or p_object_name is not null then
3414: hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
3415: hr_utility.set_message_token('API_NAME', l_proc);
3416: hr_utility.set_message_token('ARGUMENT', 'object_id, object_name');
3417: end if;
3418: end if;
3419: --
3420: --
3471: nvl(p_line_score,hr_api.g_number) OR
3472: nvl(per_cel_shd.g_old_rec.qualification_type_id,hr_api.g_number) <>
3473: nvl(p_qualification_type_id,hr_api.g_number))
3474: OR NOT l_api_updating) then
3475: hr_utility.set_location(l_proc, 6);
3476: --
3477: -- Check the parameters status when the type is 'REQUIREMENT'
3478: --
3479: if p_type = 'REQUIREMENT' then
3483: if (p_organization_id is NULL AND
3484: p_job_id is null AND p_position_id IS NULL AND
3485: p_enterprise_id is null) then
3486: --
3487: hr_utility.set_location(l_proc, 7);
3488: hr_utility.set_message(801,'HR_51655_CEL_ORG_JOB_POS');
3489:
3490: hr_utility.raise_error;
3491: --
3484: p_job_id is null AND p_position_id IS NULL AND
3485: p_enterprise_id is null) then
3486: --
3487: hr_utility.set_location(l_proc, 7);
3488: hr_utility.set_message(801,'HR_51655_CEL_ORG_JOB_POS');
3489:
3490: hr_utility.raise_error;
3491: --
3492: elsif ((p_organization_id is not null OR p_enterprise_id is not NULL)
3486: --
3487: hr_utility.set_location(l_proc, 7);
3488: hr_utility.set_message(801,'HR_51655_CEL_ORG_JOB_POS');
3489:
3490: hr_utility.raise_error;
3491: --
3492: elsif ((p_organization_id is not null OR p_enterprise_id is not NULL)
3493: AND (p_valid_grade_id is not null) ) then
3494: hr_utility.set_location(l_proc, 8);
3490: hr_utility.raise_error;
3491: --
3492: elsif ((p_organization_id is not null OR p_enterprise_id is not NULL)
3493: AND (p_valid_grade_id is not null) ) then
3494: hr_utility.set_location(l_proc, 8);
3495: hr_utility.set_message(800,'HR_52373_CEL_GRD_ID_MST_NULL');
3496: hr_multi_message.add
3497: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID');
3498:
3491: --
3492: elsif ((p_organization_id is not null OR p_enterprise_id is not NULL)
3493: AND (p_valid_grade_id is not null) ) then
3494: hr_utility.set_location(l_proc, 8);
3495: hr_utility.set_message(800,'HR_52373_CEL_GRD_ID_MST_NULL');
3496: hr_multi_message.add
3497: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID');
3498:
3499: hr_utility.raise_error;
3495: hr_utility.set_message(800,'HR_52373_CEL_GRD_ID_MST_NULL');
3496: hr_multi_message.add
3497: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID');
3498:
3499: hr_utility.raise_error;
3500: --
3501: elsif ((p_enterprise_id is not null AND(p_organization_id
3502: is not null OR p_job_id is not null OR p_position_id is not null))
3503: OR
3509: OR
3510: (p_position_id is not null AND(p_enterprise_id is not null OR
3511: p_job_id is not null OR p_organization_id is not null))) then
3512: --
3513: hr_utility.set_location(l_proc, 10);
3514: hr_utility.set_message(801,'HR_51656_CEL_ORG_JOB_POS_ENT');
3515: hr_utility.raise_error;
3516: --
3517: elsif(p_assessment_id is not null
3510: (p_position_id is not null AND(p_enterprise_id is not null OR
3511: p_job_id is not null OR p_organization_id is not null))) then
3512: --
3513: hr_utility.set_location(l_proc, 10);
3514: hr_utility.set_message(801,'HR_51656_CEL_ORG_JOB_POS_ENT');
3515: hr_utility.raise_error;
3516: --
3517: elsif(p_assessment_id is not null
3518: OR p_assessment_type_id is not null
3511: p_job_id is not null OR p_organization_id is not null))) then
3512: --
3513: hr_utility.set_location(l_proc, 10);
3514: hr_utility.set_message(801,'HR_51656_CEL_ORG_JOB_POS_ENT');
3515: hr_utility.raise_error;
3516: --
3517: elsif(p_assessment_id is not null
3518: OR p_assessment_type_id is not null
3519: OR p_activity_version_id is not null
3535: OR p_competence_id is NULL
3536: OR p_mandatory is null)
3537: then
3538: --
3539: hr_utility.set_location(l_proc, 15);
3540: hr_utility.set_message(801,'HR_51657_CEL_REQ_TYPE_ERROR');
3541: hr_utility.raise_error;
3542: --
3543: end if;
3536: OR p_mandatory is null)
3537: then
3538: --
3539: hr_utility.set_location(l_proc, 15);
3540: hr_utility.set_message(801,'HR_51657_CEL_REQ_TYPE_ERROR');
3541: hr_utility.raise_error;
3542: --
3543: end if;
3544: end if;
3537: then
3538: --
3539: hr_utility.set_location(l_proc, 15);
3540: hr_utility.set_message(801,'HR_51657_CEL_REQ_TYPE_ERROR');
3541: hr_utility.raise_error;
3542: --
3543: end if;
3544: end if;
3545: --
3569: OR p_next_certification_date is not null
3570: )
3571: then
3572: --
3573: hr_utility.set_location(l_proc, 20);
3574: hr_utility.set_message(801,'HR_51658_CEL_ASS_TYPE_ERROR');
3575: hr_utility.raise_error;
3576: --
3577: end if;
3570: )
3571: then
3572: --
3573: hr_utility.set_location(l_proc, 20);
3574: hr_utility.set_message(801,'HR_51658_CEL_ASS_TYPE_ERROR');
3575: hr_utility.raise_error;
3576: --
3577: end if;
3578: --
3571: then
3572: --
3573: hr_utility.set_location(l_proc, 20);
3574: hr_utility.set_message(801,'HR_51658_CEL_ASS_TYPE_ERROR');
3575: hr_utility.raise_error;
3576: --
3577: end if;
3578: --
3579: elsif (p_type = 'ASSESSMENT_GROUP') then
3605: OR p_rating_level_id is not null
3606: )
3607: then
3608: --
3609: hr_utility.set_location(l_proc, 25);
3610: hr_utility.set_message(801,'HR_51659_CEL_ASS_GRP_ERROR');
3611: hr_utility.raise_error;
3612: --
3613: end if;
3606: )
3607: then
3608: --
3609: hr_utility.set_location(l_proc, 25);
3610: hr_utility.set_message(801,'HR_51659_CEL_ASS_GRP_ERROR');
3611: hr_utility.raise_error;
3612: --
3613: end if;
3614: elsif (p_type = 'ASSESSMENT_COMPETENCE') then
3607: then
3608: --
3609: hr_utility.set_location(l_proc, 25);
3610: hr_utility.set_message(801,'HR_51659_CEL_ASS_GRP_ERROR');
3611: hr_utility.raise_error;
3612: --
3613: end if;
3614: elsif (p_type = 'ASSESSMENT_COMPETENCE') then
3615: if(p_parent_competence_element_id IS NOT NULL) then
3621: -- raise an error message because the
3622: -- parent_competence_element type is not
3623: -- 'ASSESSMENT_GROUP'
3624: --
3625: hr_utility.set_location(l_proc, 30);
3626: hr_utility.set_message(801,'HR_51660_CEL_ASS_COMP_ERROR');
3627: hr_utility.raise_error;
3628: end if;
3629: close csr_parent_comp_element;
3622: -- parent_competence_element type is not
3623: -- 'ASSESSMENT_GROUP'
3624: --
3625: hr_utility.set_location(l_proc, 30);
3626: hr_utility.set_message(801,'HR_51660_CEL_ASS_COMP_ERROR');
3627: hr_utility.raise_error;
3628: end if;
3629: close csr_parent_comp_element;
3630: end if;
3623: -- 'ASSESSMENT_GROUP'
3624: --
3625: hr_utility.set_location(l_proc, 30);
3626: hr_utility.set_message(801,'HR_51660_CEL_ASS_COMP_ERROR');
3627: hr_utility.raise_error;
3628: end if;
3629: close csr_parent_comp_element;
3630: end if;
3631: if ((p_assessment_type_id is null AND p_parent_competence_element_id
3634:
3635: (p_assessment_type_id is not null AND
3636: p_parent_competence_element_id is not null )) then
3637: --
3638: hr_utility.set_location(l_proc, 35);
3639: hr_utility.set_message(801,'HR_51662_CEL_ASS_COMP_MUTA');
3640: hr_utility.raise_error;
3641: --
3642: end if;
3635: (p_assessment_type_id is not null AND
3636: p_parent_competence_element_id is not null )) then
3637: --
3638: hr_utility.set_location(l_proc, 35);
3639: hr_utility.set_message(801,'HR_51662_CEL_ASS_COMP_MUTA');
3640: hr_utility.raise_error;
3641: --
3642: end if;
3643: if(p_assessment_id is not null
3636: p_parent_competence_element_id is not null )) then
3637: --
3638: hr_utility.set_location(l_proc, 35);
3639: hr_utility.set_message(801,'HR_51662_CEL_ASS_COMP_MUTA');
3640: hr_utility.raise_error;
3641: --
3642: end if;
3643: if(p_assessment_id is not null
3644: OR p_competence_id is NULL
3666: OR p_rating_level_id is not null
3667: )
3668: then
3669: --
3670: hr_utility.set_location(l_proc, 40);
3671: hr_utility.set_message(801,'HR_51663_CEL_ASS_COMP_ERR');
3672: hr_utility.raise_error;
3673:
3674: --
3667: )
3668: then
3669: --
3670: hr_utility.set_location(l_proc, 40);
3671: hr_utility.set_message(801,'HR_51663_CEL_ASS_COMP_ERR');
3672: hr_utility.raise_error;
3673:
3674: --
3675: end if;
3668: then
3669: --
3670: hr_utility.set_location(l_proc, 40);
3671: hr_utility.set_message(801,'HR_51663_CEL_ASS_COMP_ERR');
3672: hr_utility.raise_error;
3673:
3674: --
3675: end if;
3676: --
3704: OR p_rating_level_id is not null
3705: )
3706: then
3707: --
3708: hr_utility.set_location(l_proc, 45);
3709: hr_utility.set_message(801,'HR_51664_CEL_COMP_USG_ERR');
3710: hr_utility.raise_error;
3711: --
3712: end if;
3705: )
3706: then
3707: --
3708: hr_utility.set_location(l_proc, 45);
3709: hr_utility.set_message(801,'HR_51664_CEL_COMP_USG_ERR');
3710: hr_utility.raise_error;
3711: --
3712: end if;
3713:
3706: then
3707: --
3708: hr_utility.set_location(l_proc, 45);
3709: hr_utility.set_message(801,'HR_51664_CEL_COMP_USG_ERR');
3710: hr_utility.raise_error;
3711: --
3712: end if;
3713:
3714: --
3715: elsif (p_type = 'DELIVERY') then
3716: if (p_activity_version_id is NULL)
3717: then
3718: --
3719: hr_utility.set_location(l_proc, 50);
3720: hr_utility.set_message(801,'HR_51665_CEL_DELVR_ERROR');
3721: hr_utility.raise_error;
3722: --
3723: elsif(p_assessment_id is not null
3716: if (p_activity_version_id is NULL)
3717: then
3718: --
3719: hr_utility.set_location(l_proc, 50);
3720: hr_utility.set_message(801,'HR_51665_CEL_DELVR_ERROR');
3721: hr_utility.raise_error;
3722: --
3723: elsif(p_assessment_id is not null
3724: OR p_assessment_type_id is not null
3717: then
3718: --
3719: hr_utility.set_location(l_proc, 50);
3720: hr_utility.set_message(801,'HR_51665_CEL_DELVR_ERROR');
3721: hr_utility.raise_error;
3722: --
3723: elsif(p_assessment_id is not null
3724: OR p_assessment_type_id is not null
3725: OR p_organization_id is not null
3746: OR p_effective_date_from is NULL
3747: )
3748: then
3749: --
3750: hr_utility.set_location(l_proc, 55);
3751: hr_utility.set_message(801,'HR_51666_CEL_DELVR_ERROR');
3752:
3753: hr_utility.raise_error;
3754: --
3747: )
3748: then
3749: --
3750: hr_utility.set_location(l_proc, 55);
3751: hr_utility.set_message(801,'HR_51666_CEL_DELVR_ERROR');
3752:
3753: hr_utility.raise_error;
3754: --
3755: end if;
3749: --
3750: hr_utility.set_location(l_proc, 55);
3751: hr_utility.set_message(801,'HR_51666_CEL_DELVR_ERROR');
3752:
3753: hr_utility.raise_error;
3754: --
3755: end if;
3756: --
3757: -- Note: The PREREQUISITE type is the same as the DELIVERY
3761: elsif (p_type = 'PREREQUISITE') then
3762: if ( p_activity_version_id is NULL)
3763: then
3764: --
3765: hr_utility.set_location(l_proc, 60);
3766:
3767: hr_utility.set_message(801,'HR_51667_CEL_PRE_REQ_MUTA');
3768: hr_utility.raise_error;
3769: --
3763: then
3764: --
3765: hr_utility.set_location(l_proc, 60);
3766:
3767: hr_utility.set_message(801,'HR_51667_CEL_PRE_REQ_MUTA');
3768: hr_utility.raise_error;
3769: --
3770: elsif(p_assessment_id is not null
3771: OR p_assessment_type_id is not null
3764: --
3765: hr_utility.set_location(l_proc, 60);
3766:
3767: hr_utility.set_message(801,'HR_51667_CEL_PRE_REQ_MUTA');
3768: hr_utility.raise_error;
3769: --
3770: elsif(p_assessment_id is not null
3771: OR p_assessment_type_id is not null
3772: OR p_organization_id is not null
3793: OR p_mandatory is NULL
3794: )
3795: then
3796: --
3797: hr_utility.set_location(l_proc, 65);
3798: hr_utility.set_message(801,'HR_51668_CEL_PRE_REQ_ERR');
3799: hr_utility.raise_error;
3800: --
3801: end if;
3794: )
3795: then
3796: --
3797: hr_utility.set_location(l_proc, 65);
3798: hr_utility.set_message(801,'HR_51668_CEL_PRE_REQ_ERR');
3799: hr_utility.raise_error;
3800: --
3801: end if;
3802: --
3795: then
3796: --
3797: hr_utility.set_location(l_proc, 65);
3798: hr_utility.set_message(801,'HR_51668_CEL_PRE_REQ_ERR');
3799: hr_utility.raise_error;
3800: --
3801: end if;
3802: --
3803: elsif (p_type = 'PATH') then
3828: )
3829:
3830: then
3831: --
3832: hr_utility.set_location(l_proc, 70);
3833: hr_utility.set_message(801,'HR_51669_CEL_PATH_ERROR');
3834: hr_utility.raise_error;
3835: --
3836: end if;
3829:
3830: then
3831: --
3832: hr_utility.set_location(l_proc, 70);
3833: hr_utility.set_message(801,'HR_51669_CEL_PATH_ERROR');
3834: hr_utility.raise_error;
3835: --
3836: end if;
3837: --
3830: then
3831: --
3832: hr_utility.set_location(l_proc, 70);
3833: hr_utility.set_message(801,'HR_51669_CEL_PATH_ERROR');
3834: hr_utility.raise_error;
3835: --
3836: end if;
3837: --
3838: elsif (p_type = 'PERSONAL') then
3860: OR p_competence_type is not null
3861: )
3862: then
3863: --
3864: hr_utility.set_location(l_proc, 75);
3865: hr_utility.set_message(801,'HR_51670_CEL_PER_TYPE_ERROR');
3866: hr_multi_message.add
3867: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM');
3868: hr_utility.raise_error;
3861: )
3862: then
3863: --
3864: hr_utility.set_location(l_proc, 75);
3865: hr_utility.set_message(801,'HR_51670_CEL_PER_TYPE_ERROR');
3866: hr_multi_message.add
3867: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM');
3868: hr_utility.raise_error;
3869: --
3864: hr_utility.set_location(l_proc, 75);
3865: hr_utility.set_message(801,'HR_51670_CEL_PER_TYPE_ERROR');
3866: hr_multi_message.add
3867: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM');
3868: hr_utility.raise_error;
3869: --
3870: end if;
3871: --
3872: elsif (p_type = 'PROPOSAL') then
3895:
3896: )
3897: then
3898: --
3899: hr_utility.set_location(l_proc, 85);
3900: hr_utility.set_message(801,'HR_51672_CEL_PRO_TYPE_ERROR');
3901: hr_utility.raise_error;
3902: --
3903: end if;
3896: )
3897: then
3898: --
3899: hr_utility.set_location(l_proc, 85);
3900: hr_utility.set_message(801,'HR_51672_CEL_PRO_TYPE_ERROR');
3901: hr_utility.raise_error;
3902: --
3903: end if;
3904: --
3897: then
3898: --
3899: hr_utility.set_location(l_proc, 85);
3900: hr_utility.set_message(801,'HR_51672_CEL_PRO_TYPE_ERROR');
3901: hr_utility.raise_error;
3902: --
3903: end if;
3904: --
3905: elsif (p_type = 'SET') then
3932: )
3933: then
3934:
3935: --
3936: hr_utility.set_location(l_proc, 90);
3937: hr_utility.set_message(801,'HR_51673_CEL_SET_TYPE_ERROR');
3938: hr_utility.raise_error;
3939: --
3940: end if;
3933: then
3934:
3935: --
3936: hr_utility.set_location(l_proc, 90);
3937: hr_utility.set_message(801,'HR_51673_CEL_SET_TYPE_ERROR');
3938: hr_utility.raise_error;
3939: --
3940: end if;
3941: elsif (p_type = 'QUALIFICATION') then
3934:
3935: --
3936: hr_utility.set_location(l_proc, 90);
3937: hr_utility.set_message(801,'HR_51673_CEL_SET_TYPE_ERROR');
3938: hr_utility.raise_error;
3939: --
3940: end if;
3941: elsif (p_type = 'QUALIFICATION') then
3942: hr_utility.trace('date_from : ' || p_effective_date_from);
3938: hr_utility.raise_error;
3939: --
3940: end if;
3941: elsif (p_type = 'QUALIFICATION') then
3942: hr_utility.trace('date_from : ' || p_effective_date_from);
3943: hr_utility.trace('date_to : ' || p_effective_date_to);
3944: hr_utility.set_location(l_proc, 100);
3945:
3946: --
3939: --
3940: end if;
3941: elsif (p_type = 'QUALIFICATION') then
3942: hr_utility.trace('date_from : ' || p_effective_date_from);
3943: hr_utility.trace('date_to : ' || p_effective_date_to);
3944: hr_utility.set_location(l_proc, 100);
3945:
3946: --
3947: -- Mandatory parameter check
3940: end if;
3941: elsif (p_type = 'QUALIFICATION') then
3942: hr_utility.trace('date_from : ' || p_effective_date_from);
3943: hr_utility.trace('date_to : ' || p_effective_date_to);
3944: hr_utility.set_location(l_proc, 100);
3945:
3946: --
3947: -- Mandatory parameter check
3948: --
3956: p_argument => 'effective_date_from',
3957: p_argument_value => p_effective_date_from);
3958: --
3959: if (NOT l_api_updating) then
3960: hr_utility.set_location(l_proc, 103);
3961: --
3962: -- Mandatory parameter check
3963: --
3964: hr_api.mandatory_arg_error
3969: open csr_comp_qual_link;
3970: fetch csr_comp_qual_link into l_exists;
3971: if csr_comp_qual_link%FOUND then
3972: close csr_comp_qual_link;
3973: hr_utility.set_message(800,'HR_449136_QUA_FWK_LINK_EXISTS');
3974: hr_utility.raise_error;
3975: end if;
3976: close csr_comp_qual_link;
3977: else
3970: fetch csr_comp_qual_link into l_exists;
3971: if csr_comp_qual_link%FOUND then
3972: close csr_comp_qual_link;
3973: hr_utility.set_message(800,'HR_449136_QUA_FWK_LINK_EXISTS');
3974: hr_utility.raise_error;
3975: end if;
3976: close csr_comp_qual_link;
3977: else
3978: hr_utility.set_location(l_proc, 105);
3974: hr_utility.raise_error;
3975: end if;
3976: close csr_comp_qual_link;
3977: else
3978: hr_utility.set_location(l_proc, 105);
3979:
3980: open csr_upd_comp_qual_link;
3981: fetch csr_upd_comp_qual_link into l_exists;
3982: if csr_upd_comp_qual_link%FOUND then
3980: open csr_upd_comp_qual_link;
3981: fetch csr_upd_comp_qual_link into l_exists;
3982: if csr_upd_comp_qual_link%FOUND then
3983: close csr_upd_comp_qual_link;
3984: hr_utility.set_message(800,'HR_449136_QUA_FWK_LINK_EXISTS');
3985: hr_utility.raise_error;
3986: end if;
3987: close csr_upd_comp_qual_link;
3988: end if;
3981: fetch csr_upd_comp_qual_link into l_exists;
3982: if csr_upd_comp_qual_link%FOUND then
3983: close csr_upd_comp_qual_link;
3984: hr_utility.set_message(800,'HR_449136_QUA_FWK_LINK_EXISTS');
3985: hr_utility.raise_error;
3986: end if;
3987: close csr_upd_comp_qual_link;
3988: end if;
3989: end if;
3986: end if;
3987: close csr_upd_comp_qual_link;
3988: end if;
3989: end if;
3990: hr_utility.set_location('Leaving: ' || l_proc, 110);
3991: end if;
3992: end if; -- check for no_exclusive_error for TYPE.
3993: exception
3994: when app_exception.application_exception then
3994: when app_exception.application_exception then
3995: if hr_multi_message.exception_add
3996: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.TYPE'
3997: ) then
3998: hr_utility.set_location(' Leaving:'||l_proc,110);
3999: raise;
4000: end if;
4001: hr_utility.set_location(' Leaving:'||l_proc,120);
4002: end chk_type_and_validation;
3997: ) then
3998: hr_utility.set_location(' Leaving:'||l_proc,110);
3999: raise;
4000: end if;
4001: hr_utility.set_location(' Leaving:'||l_proc,120);
4002: end chk_type_and_validation;
4003: --
4004: --
4005: --
4093: l_api_updating boolean;
4094: l_exists varchar2(1);
4095: --
4096: begin
4097: hr_utility.set_location('Entering:'|| l_proc, 1);
4098: if hr_multi_message.no_exclusive_error
4099: (p_check_column1 => 'PER_COMPETENCE_ELEMENTS.TYPE') then
4100: --
4101: -- Check mandatory parameters have being set.
4133: nvl(p_competence_type,hr_api.g_varchar2) OR
4134: nvl(per_cel_shd.g_old_rec.effective_date_from,hr_api.g_date) <>
4135: nvl(p_effective_date_from,hr_api.g_date))
4136: OR NOT l_api_updating) then
4137: hr_utility.set_location(l_proc, 6);
4138: --
4139: -- build the NATIVE dynamic SQL
4140: -- note: native dynamic SQL has been used for performance
4141: l_sql_stmt := 'SELECT NULL '||
4274: ELSE
4275: l_sql_stmt := l_sql_stmt||
4276: 'AND :p_qualification_type_id IS NULL /* qualification_type_id IS NULL */ ';
4277: END IF;
4278: hr_utility.set_location(l_proc,10);
4279: --hr_utility.trace('l_sql_stmt : ' || l_sql_stmt);
4280: -- dynamically execute the SQL
4281: BEGIN
4282: EXECUTE IMMEDIATE l_sql_stmt
4275: l_sql_stmt := l_sql_stmt||
4276: 'AND :p_qualification_type_id IS NULL /* qualification_type_id IS NULL */ ';
4277: END IF;
4278: hr_utility.set_location(l_proc,10);
4279: --hr_utility.trace('l_sql_stmt : ' || l_sql_stmt);
4280: -- dynamically execute the SQL
4281: BEGIN
4282: EXECUTE IMMEDIATE l_sql_stmt
4283: INTO l_exists
4302: p_party_id, -- HR/TCA merge
4303: p_qualification_type_id;
4304: -- executed successful therefore a row has been found
4305: if p_type = 'COMPETENCE_USAGE' then
4306: hr_utility.set_message(800,'HR_52262_CEL_UNIQUE_COMP_USAGE');
4307: elsif p_type = 'ASSESSMENT' then
4308: hr_utility.set_message(800,'HR_52263_CEL_UNIQUE_ASSESSMENT');
4309: elsif p_type = 'ASSESSMNET_COMPETENCE' then
4310: hr_utility.set_message(800,'HR_52264_CEL_UNIQUE_ASM_COMP');
4304: -- executed successful therefore a row has been found
4305: if p_type = 'COMPETENCE_USAGE' then
4306: hr_utility.set_message(800,'HR_52262_CEL_UNIQUE_COMP_USAGE');
4307: elsif p_type = 'ASSESSMENT' then
4308: hr_utility.set_message(800,'HR_52263_CEL_UNIQUE_ASSESSMENT');
4309: elsif p_type = 'ASSESSMNET_COMPETENCE' then
4310: hr_utility.set_message(800,'HR_52264_CEL_UNIQUE_ASM_COMP');
4311: elsif p_type = 'ASSESSMENT_GROUP' then
4312: hr_utility.set_message(800,'HR_52265_CEL_UNIQUE_ASM_GROUP');
4306: hr_utility.set_message(800,'HR_52262_CEL_UNIQUE_COMP_USAGE');
4307: elsif p_type = 'ASSESSMENT' then
4308: hr_utility.set_message(800,'HR_52263_CEL_UNIQUE_ASSESSMENT');
4309: elsif p_type = 'ASSESSMNET_COMPETENCE' then
4310: hr_utility.set_message(800,'HR_52264_CEL_UNIQUE_ASM_COMP');
4311: elsif p_type = 'ASSESSMENT_GROUP' then
4312: hr_utility.set_message(800,'HR_52265_CEL_UNIQUE_ASM_GROUP');
4313: elsif p_type = 'REQUIREMENT' then
4314: hr_utility.set_message(800,'HR_52266_CEL_UNIQUE_REQUIREMEN');
4308: hr_utility.set_message(800,'HR_52263_CEL_UNIQUE_ASSESSMENT');
4309: elsif p_type = 'ASSESSMNET_COMPETENCE' then
4310: hr_utility.set_message(800,'HR_52264_CEL_UNIQUE_ASM_COMP');
4311: elsif p_type = 'ASSESSMENT_GROUP' then
4312: hr_utility.set_message(800,'HR_52265_CEL_UNIQUE_ASM_GROUP');
4313: elsif p_type = 'REQUIREMENT' then
4314: hr_utility.set_message(800,'HR_52266_CEL_UNIQUE_REQUIREMEN');
4315: elsif p_type = 'DELIVERY' then
4316: hr_utility.set_message(800,'HR_52267_CEL_UNIQUE_DELIVERY');
4310: hr_utility.set_message(800,'HR_52264_CEL_UNIQUE_ASM_COMP');
4311: elsif p_type = 'ASSESSMENT_GROUP' then
4312: hr_utility.set_message(800,'HR_52265_CEL_UNIQUE_ASM_GROUP');
4313: elsif p_type = 'REQUIREMENT' then
4314: hr_utility.set_message(800,'HR_52266_CEL_UNIQUE_REQUIREMEN');
4315: elsif p_type = 'DELIVERY' then
4316: hr_utility.set_message(800,'HR_52267_CEL_UNIQUE_DELIVERY');
4317: elsif p_type = 'PERSONAL' then
4318: hr_utility.set_message(800,'HR_52268_CEL_UNIQUE_PERSONAL');
4312: hr_utility.set_message(800,'HR_52265_CEL_UNIQUE_ASM_GROUP');
4313: elsif p_type = 'REQUIREMENT' then
4314: hr_utility.set_message(800,'HR_52266_CEL_UNIQUE_REQUIREMEN');
4315: elsif p_type = 'DELIVERY' then
4316: hr_utility.set_message(800,'HR_52267_CEL_UNIQUE_DELIVERY');
4317: elsif p_type = 'PERSONAL' then
4318: hr_utility.set_message(800,'HR_52268_CEL_UNIQUE_PERSONAL');
4319: else
4320: hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4314: hr_utility.set_message(800,'HR_52266_CEL_UNIQUE_REQUIREMEN');
4315: elsif p_type = 'DELIVERY' then
4316: hr_utility.set_message(800,'HR_52267_CEL_UNIQUE_DELIVERY');
4317: elsif p_type = 'PERSONAL' then
4318: hr_utility.set_message(800,'HR_52268_CEL_UNIQUE_PERSONAL');
4319: else
4320: hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4321: end if;
4322: --
4316: hr_utility.set_message(800,'HR_52267_CEL_UNIQUE_DELIVERY');
4317: elsif p_type = 'PERSONAL' then
4318: hr_utility.set_message(800,'HR_52268_CEL_UNIQUE_PERSONAL');
4319: else
4320: hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4321: end if;
4322: --
4323: hr_utility.raise_error;
4324: EXCEPTION
4319: else
4320: hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4321: end if;
4322: --
4323: hr_utility.raise_error;
4324: EXCEPTION
4325: WHEN NO_DATA_FOUND THEN
4326: null;
4327: END;
4325: WHEN NO_DATA_FOUND THEN
4326: null;
4327: END;
4328: end if;
4329: hr_utility.set_location(l_proc,7);
4330: --
4331: end if; -- no_exclusive_check for TYPE
4332: hr_utility.set_location('Leaving: ' || l_proc, 10);
4333: exception
4328: end if;
4329: hr_utility.set_location(l_proc,7);
4330: --
4331: end if; -- no_exclusive_check for TYPE
4332: hr_utility.set_location('Leaving: ' || l_proc, 10);
4333: exception
4334: when app_exception.application_exception then
4335: hr_multi_message.add;
4336: hr_utility.set_location(' Leaving:'||l_proc,105);
4332: hr_utility.set_location('Leaving: ' || l_proc, 10);
4333: exception
4334: when app_exception.application_exception then
4335: hr_multi_message.add;
4336: hr_utility.set_location(' Leaving:'||l_proc,105);
4337: end chk_unique_competence_element;
4338: /*
4339: --
4340: -- ----------------------------------------------------------------------------
4383: and p_effective_date between effective_date_from
4384: and nvl(effective_date_to,hr_api.g_eot);
4385: Begin
4386: --
4387: hr_utility.set_location('Entering:'||l_proc,10);
4388: if p_qualification_type_id is not NULL and p_competence_id is not NULL
4389: then
4390: --
4391: -- Only proceed with validation if :
4401: hr_api.g_number) = nvl(p_competence_id, hr_api.g_number)
4402: and nvl(per_cel_shd.g_old_rec.qualification_type_id,hr_api.g_number)
4403: = nvl(p_qualification_type_id, hr_api.g_number)
4404: ) then
4405: hr_utility.set_location('Leaving.... ' || l_proc,20);
4406: return;
4407: end if;
4408:
4409: hr_utility.set_location(l_proc,20);
4405: hr_utility.set_location('Leaving.... ' || l_proc,20);
4406: return;
4407: end if;
4408:
4409: hr_utility.set_location(l_proc,20);
4410:
4411: open csr_unique_comp_qual;
4412: fetch csr_unique_comp_qual into l_exists;
4413: if csr_unique_comp_qual%found then
4412: fetch csr_unique_comp_qual into l_exists;
4413: if csr_unique_comp_qual%found then
4414: close csr_unique_comp_qual;
4415: --
4416: hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4417: hr_utility.raise_error;
4418: --
4419: end if;
4420: close csr_unique_comp_qual;
4413: if csr_unique_comp_qual%found then
4414: close csr_unique_comp_qual;
4415: --
4416: hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4417: hr_utility.raise_error;
4418: --
4419: end if;
4420: close csr_unique_comp_qual;
4421: end if;
4419: end if;
4420: close csr_unique_comp_qual;
4421: end if;
4422:
4423: hr_utility.set_location('Leaving:'||l_proc,30);
4424: --
4425: End chk_unique_comp_qual;
4426: */
4427: --
4553:
4554: --
4555: begin
4556: --
4557: hr_utility.set_location('Entering:'|| l_proc, 1);
4558: --
4559: -- Only do the delete validation if the type is
4560: -- ASSESSESSMENT_GROUP, ASSESSMENT_COMPETENCE, COMPETENCE_USAGE or
4561: -- or PERSONAL,ASSESSMENT.
4569: -- raise an error message if the type = 'PERSONAL'
4570: --
4571: -- commented out following section due to bug raised (no. 525537) 1-09-97
4572: -- if p_type = 'PERSONAL' then
4573: -- hr_utility.set_location(l_proc,5);
4574: -- hr_utility.set_message(801,'HR_51675_CEL_PER_CANT_DEL');
4575: -- hr_utility.raise_error;
4576: -- end if;
4577: --
4570: --
4571: -- commented out following section due to bug raised (no. 525537) 1-09-97
4572: -- if p_type = 'PERSONAL' then
4573: -- hr_utility.set_location(l_proc,5);
4574: -- hr_utility.set_message(801,'HR_51675_CEL_PER_CANT_DEL');
4575: -- hr_utility.raise_error;
4576: -- end if;
4577: --
4578: --
4571: -- commented out following section due to bug raised (no. 525537) 1-09-97
4572: -- if p_type = 'PERSONAL' then
4573: -- hr_utility.set_location(l_proc,5);
4574: -- hr_utility.set_message(801,'HR_51675_CEL_PER_CANT_DEL');
4575: -- hr_utility.raise_error;
4576: -- end if;
4577: --
4578: --
4579: if(p_type = 'ASSESSMENT_GROUP') then
4584: open csr_is_parent_comp;
4585: fetch csr_is_parent_comp into l_exists;
4586: if csr_is_parent_comp%found then
4587: close csr_is_parent_comp;
4588: hr_utility.set_location(l_proc,15);
4589: hr_utility.set_message(801,'HR_51677_CEL_PARNT_CANT_DEL');
4590: hr_utility.raise_error;
4591:
4592: end if;
4585: fetch csr_is_parent_comp into l_exists;
4586: if csr_is_parent_comp%found then
4587: close csr_is_parent_comp;
4588: hr_utility.set_location(l_proc,15);
4589: hr_utility.set_message(801,'HR_51677_CEL_PARNT_CANT_DEL');
4590: hr_utility.raise_error;
4591:
4592: end if;
4593: close csr_is_parent_comp;
4586: if csr_is_parent_comp%found then
4587: close csr_is_parent_comp;
4588: hr_utility.set_location(l_proc,15);
4589: hr_utility.set_message(801,'HR_51677_CEL_PARNT_CANT_DEL');
4590: hr_utility.raise_error;
4591:
4592: end if;
4593: close csr_is_parent_comp;
4594: end if;
4603: fetch csr_get_comp_group into l_exists;
4604: if csr_get_comp_group%found then
4605:
4606: close csr_get_comp_group;
4607: hr_utility.set_location(l_proc,20);
4608: hr_utility.set_message(801,'HR_51678_CEL_COM_USG_CANT_DEL');
4609: hr_utility.raise_error;
4610: end if;
4611: close csr_get_comp_group;
4604: if csr_get_comp_group%found then
4605:
4606: close csr_get_comp_group;
4607: hr_utility.set_location(l_proc,20);
4608: hr_utility.set_message(801,'HR_51678_CEL_COM_USG_CANT_DEL');
4609: hr_utility.raise_error;
4610: end if;
4611: close csr_get_comp_group;
4612: end if;
4605:
4606: close csr_get_comp_group;
4607: hr_utility.set_location(l_proc,20);
4608: hr_utility.set_message(801,'HR_51678_CEL_COM_USG_CANT_DEL');
4609: hr_utility.raise_error;
4610: end if;
4611: close csr_get_comp_group;
4612: end if;
4613: --
4620: open csr_get_used_comp_element;
4621: fetch csr_get_used_comp_element into l_exists;
4622: if csr_get_used_comp_element%found then
4623: close csr_get_used_comp_element;
4624: hr_utility.set_location(l_proc,25);
4625: hr_utility.set_message(801,'HR_51679_CEL_ASS_COMP_CANT_DEL');
4626: hr_utility.raise_error;
4627: end if;
4628: --
4621: fetch csr_get_used_comp_element into l_exists;
4622: if csr_get_used_comp_element%found then
4623: close csr_get_used_comp_element;
4624: hr_utility.set_location(l_proc,25);
4625: hr_utility.set_message(801,'HR_51679_CEL_ASS_COMP_CANT_DEL');
4626: hr_utility.raise_error;
4627: end if;
4628: --
4629: close csr_get_used_comp_element;
4622: if csr_get_used_comp_element%found then
4623: close csr_get_used_comp_element;
4624: hr_utility.set_location(l_proc,25);
4625: hr_utility.set_message(801,'HR_51679_CEL_ASS_COMP_CANT_DEL');
4626: hr_utility.raise_error;
4627: end if;
4628: --
4629: close csr_get_used_comp_element;
4630: end if;
4630: end if;
4631: --
4632: end if;
4633: --
4634: hr_utility.set_location(l_proc,30);
4635:
4636: open csr_comp_element_outcome;
4637: fetch csr_comp_element_outcome into l_exists;
4638: if csr_comp_element_outcome%FOUND then
4636: open csr_comp_element_outcome;
4637: fetch csr_comp_element_outcome into l_exists;
4638: if csr_comp_element_outcome%FOUND then
4639: close csr_comp_element_outcome;
4640: hr_utility.set_message(800,'HR_449135_QUA_FWK_CEL_TAB_REF');
4641: hr_utility.raise_error;
4642: end if;
4643: close csr_comp_element_outcome;
4644:
4637: fetch csr_comp_element_outcome into l_exists;
4638: if csr_comp_element_outcome%FOUND then
4639: close csr_comp_element_outcome;
4640: hr_utility.set_message(800,'HR_449135_QUA_FWK_CEL_TAB_REF');
4641: hr_utility.raise_error;
4642: end if;
4643: close csr_comp_element_outcome;
4644:
4645: hr_utility.set_location('Leaving: ' || l_proc,40);
4641: hr_utility.raise_error;
4642: end if;
4643: close csr_comp_element_outcome;
4644:
4645: hr_utility.set_location('Leaving: ' || l_proc,40);
4646: end chk_comp_element_delete;
4647: -- -----------------------------------------------------------------------
4648: -- |------------------------------< chk_df >-----------------------------|
4649: -- -----------------------------------------------------------------------
4676: --
4677: l_proc varchar2(72) := g_package||'chk_df';
4678: --
4679: begin
4680: hr_utility.set_location('Entering:'||l_proc, 10);
4681: --
4682: if ((p_rec.valid_grade_id is not null) and (
4683: nvl(per_cel_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
4684: nvl(p_rec.attribute_category, hr_api.g_varchar2) or
4776: ,p_attribute20_value => p_rec.attribute20
4777: );
4778: end if;
4779: --
4780: hr_utility.set_location(' Leaving:'||l_proc, 20);
4781:
4782: end chk_df;
4783: --
4784: -- ----------------------------------------------------------------------------
4815: --
4816: l_proc varchar2(72) := g_package || 'chk_ddf';
4817: --
4818: begin
4819: hr_utility.set_location('Entering:'||l_proc,10);
4820: --
4821: if ((p_rec.competence_element_id is not null) and (
4822: nvl(per_cel_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
4823: nvl(p_rec.information_category, hr_api.g_varchar2) or
4912: ,p_attribute20_value => p_rec.information20
4913: );
4914: end if;
4915: --
4916: hr_utility.set_location(' Leaving:'||l_proc,20);
4917: end chk_ddf;
4918: --
4919: -- ----------------------------------------------------------------------------
4920: -- |---------------------------< insert_validate >----------------------------|
4924: --
4925: l_proc varchar2(72) := g_package||'insert_validate';
4926: --
4927: Begin
4928: hr_utility.set_location('Entering:'||l_proc, 5);
4929: --
4930: -- Validate important attributes
4931: --
4932: -- Call all supporting business operations
4971: ,p_type => p_rec.type
4972: ,p_object_version_number => p_rec.object_version_number
4973: );
4974: --
4975: hr_utility.set_location(l_proc, 10);
4976: --
4977: --
4978: -- Business Rule Mapping
4979: -- =====================
5024: ,p_qualification_type_id => p_rec.qualification_type_id
5025: );
5026: --
5027:
5028: hr_utility.set_location(l_proc, 15);
5029: --
5030: -- Business Rule Mapping
5031: -- =====================
5032: -- CHK_UNIQUE_COMPETENCE_ELEMENT
5059: ,p_party_id => p_rec.party_id -- HR/TCA merge
5060: ,p_qualification_type_id => p_rec.qualification_type_id
5061: );
5062: --
5063: hr_utility.set_location(l_proc, 16);
5064: --
5065: -- Business Rule Mapping
5066: -- =====================
5067: -- CHK_CERTIFICATION_METHOD
5072: ,p_certification_method => p_rec.certification_method
5073: ,p_object_version_number => p_rec.object_version_number
5074: );
5075: --
5076: hr_utility.set_location(l_proc, 20);
5077: --
5078: -- Business Rule Mapping
5079: -- =====================
5080: -- CHK_COMPETENCE_TYPE
5085: ,p_competence_type => p_rec.competence_type
5086: ,p_object_version_number => p_rec.object_version_number
5087: );
5088: --
5089: hr_utility.set_location(l_proc, 25);
5090: --
5091: -- Business Rule Mapping
5092: -- =====================
5093: -- CHK_GROUP_COMPETENCE_TYPE
5098: ,p_competence_type => p_rec.group_competence_type
5099: ,p_object_version_number => p_rec.object_version_number
5100: );
5101: --
5102: hr_utility.set_location(l_proc, 30);
5103: --
5104: -- Business Rule Mapping
5105: -- =====================
5106: -- CHK_MANDATORY
5112: ,p_mandatory => p_rec.mandatory
5113: ,p_object_version_number => p_rec.object_version_number
5114: );
5115: --
5116: hr_utility.set_location(l_proc, 35);
5117: --
5118: -- Business Rule Mapping
5119: -- =====================
5120: -- CHK_SOURC_OF_PROFICIENCY_LEVEL
5127: => p_rec.source_of_proficiency_level
5128: ,p_object_version_number => p_rec.object_version_number
5129: );
5130: --
5131: hr_utility.set_location(l_proc, 40);
5132: --
5133: -- Business Rule Mapping
5134: -- =====================
5135: -- CHK_CERTIFICATION_METHOD_DATE
5141: ,p_certification_method => p_rec.certification_method
5142: ,p_object_version_number => p_rec.object_version_number
5143: );
5144: --
5145: hr_utility.set_location(l_proc, 45);
5146: --
5147: -- Business Rule Mapping
5148: -- =====================
5149: -- CHK_NEXT_CERTIFICATION_DATE
5155: ,p_object_version_number => p_rec.object_version_number
5156: ,p_effective_date_from => p_rec.effective_date_from -- added for fix of #731089
5157: );
5158: --
5159: hr_utility.set_location(l_proc, 48);
5160: --
5161: -- Business Rule Mapping
5162: -- =====================
5163: -- CHK_FOREIGN_KEYS
5192: ,p_party_id => p_rec.party_id -- HR/TCA merge
5193: ,p_qualification_type_id => p_rec.qualification_type_id
5194: );
5195: --
5196: hr_utility.set_location('Entering:'||l_proc, 50);
5197:
5198: --
5199: -- Business Rule Mapping
5200: -- =====================
5205: ,p_effective_date
5206: );
5207:
5208: --
5209: hr_utility.set_location('Entering:'||l_proc, 52);
5210:
5211: --
5212: -- Business Rule Mapping
5213: -- =====================
5223: ,p_object_version_number => p_rec.object_version_number
5224: ,p_party_id => p_rec.party_id
5225: );
5226: --
5227: hr_utility.set_location(l_proc, 55);
5228: --
5229: -- Business Rule Mapping
5230: -- =====================
5231: -- CHK_RATING_WEIGHTING_ID
5241: ,p_type => p_rec.type
5242: ,p_party_id => p_rec.party_id
5243: );
5244: --
5245: hr_utility.set_location(l_proc, 60);
5246: --
5247: -- Business Rule Mapping
5248: -- =====================
5249: -- CHK_COMPETENCE_ELEMENT_DATES
5263: ,p_object_version_number => p_rec.object_version_number
5264: ,p_enterprise_id => p_rec.enterprise_id
5265: );
5266: --
5267: hr_utility.set_location(l_proc, 65);
5268:
5269: --
5270: -- Business Rule Mapping
5271: -- =====================
5280: ,p_object_version_number => p_rec.object_version_number
5281: );
5282:
5283: --
5284: hr_utility.set_location(l_proc, 70);
5285:
5286: /*
5287: -- Business Rule Mapping
5288: -- =====================
5296: ,p_effective_date => p_effective_date
5297: );
5298: */
5299: --
5300: hr_utility.set_location(l_proc, 80);
5301: --
5302: -- do the descriptive flex validation.
5303: --
5304: per_cel_bus.chk_df(p_rec => p_rec);
5302: -- do the descriptive flex validation.
5303: --
5304: per_cel_bus.chk_df(p_rec => p_rec);
5305:
5306: hr_utility.set_location(l_proc, 90);
5307:
5308: --
5309: -- do the developer descriptive flex validation.
5310: --
5309: -- do the developer descriptive flex validation.
5310: --
5311: per_cel_bus.chk_ddf(p_rec => p_rec);
5312: --
5313: hr_utility.set_location('Leaving:'||l_proc, 100);
5314: --
5315: End insert_validate;
5316: --
5317: -- ----------------------------------------------------------------------------
5322: --
5323: l_proc varchar2(72) := g_package||'update_validate';
5324: --
5325: Begin
5326: hr_utility.set_location('Entering:'||l_proc, 5);
5327: -- Validate Important Attributes
5328: --
5329: -- Business Rule Mapping
5330: -- =====================
5360: --
5361: per_cel_bus.check_non_updateable_args
5362: (p_rec =>p_rec);
5363: --
5364: hr_utility.set_location (l_proc,6);
5365: --
5366: -- Business Rule Mapping
5367: -- =====================
5368: -- CHK_TYPE_AND_VALIDATION
5412: ,p_party_id => p_rec.party_id -- HR/TCA merge
5413: ,p_qualification_type_id => p_rec.qualification_type_id -- BUG3356369
5414: );
5415: --
5416: hr_utility.set_location(l_proc, 15);
5417:
5418: --
5419: -- Business Rule Mapping
5420: -- =====================
5448: ,p_party_id => p_rec.party_id -- HR/TCA merge
5449: ,p_qualification_type_id => p_rec.qualification_type_id
5450: );
5451: --
5452: hr_utility.set_location(l_proc, 16);
5453: --
5454: -- Business Rule Mapping
5455: -- =====================
5456: -- CHK_CERTIFICATION_METHOD
5462:
5463: ,p_object_version_number => p_rec.object_version_number
5464: );
5465: --
5466: hr_utility.set_location(l_proc, 20);
5467: --
5468: -- Business Rule Mapping
5469: -- =====================
5470: -- CHK_COMPETENCE_TYPE
5476:
5477: ,p_object_version_number => p_rec.object_version_number
5478: );
5479: --
5480: hr_utility.set_location(l_proc, 25);
5481: --
5482: -- Business Rule Mapping
5483: -- =====================
5484: -- CHK_GROUP_COMPETENCE_TYPE
5490:
5491: ,p_object_version_number => p_rec.object_version_number
5492: );
5493: --
5494: hr_utility.set_location(l_proc, 30);
5495: --
5496: -- Business Rule Mapping
5497: -- =====================
5498: -- CHK_MANDATORY
5504:
5505: ,p_object_version_number => p_rec.object_version_number
5506: );
5507: --
5508: hr_utility.set_location(l_proc, 35);
5509: --
5510: -- Business Rule Mapping
5511: -- =====================
5512: -- CHK_SOURC_OF_PROFICIENCY_LEVEL
5519: => p_rec.source_of_proficiency_level
5520: ,p_object_version_number => p_rec.object_version_number
5521: );
5522: --
5523: hr_utility.set_location(l_proc, 40);
5524: --
5525: -- Business Rule Mapping
5526: -- =====================
5527: -- CHK_CERTIFICATION_METHOD_DATE
5533: ,p_certification_method => p_rec.certification_method
5534: ,p_object_version_number => p_rec.object_version_number
5535: );
5536: --
5537: hr_utility.set_location(l_proc, 45);
5538: --
5539: -- Business Rule Mapping
5540: -- =====================
5541: -- CHK_NEXT_CERTIFICATION_DATE
5548: ,p_object_version_number => p_rec.object_version_number
5549: ,p_effective_date_from => p_rec.effective_date_from --added for bug fix of #731089
5550: );
5551: --
5552: hr_utility.set_location(l_proc, 48);
5553:
5554: -- Business Rule Mapping
5555: -- =====================
5556: -- CHK_PROFICIENCY_LEVEL_ID
5566: ,p_object_version_number => p_rec.object_version_number
5567: ,p_party_id => p_rec.party_id
5568: );
5569: --
5570: hr_utility.set_location(l_proc, 55);
5571: --
5572: -- Business Rule Mapping
5573: -- =====================
5574: -- CHK_RATING_WEIGHTING_ID
5584: ,p_type => p_rec.type
5585: ,p_party_id => p_rec.party_id
5586: );
5587: --
5588: hr_utility.set_location(l_proc, 60);
5589: --
5590: -- Business Rule Mapping
5591:
5592: -- =====================
5607: ,p_object_version_number => p_rec.object_version_number
5608: ,p_enterprise_id => p_rec.enterprise_id
5609: );
5610: --
5611: hr_utility.set_location(l_proc, 65);
5612: --
5613: -- Business Rule Mapping
5614: -- =====================
5615: -- CHK_NORMAL_ELAPSE_DURATION
5623: => p_rec.normal_elapse_duration_unit
5624: ,p_object_version_number => p_rec.object_version_number
5625: );
5626: --
5627: hr_utility.set_location(l_proc, 70);
5628: --
5629: --
5630: -- Business Rule Mapping
5631: -- =====================
5662: ,p_qualification_type_id => p_rec.qualification_type_id
5663: );
5664:
5665: --
5666: hr_utility.set_location(l_proc, 70);
5667: /*
5668: -- Business Rule Mapping
5669: -- =====================
5670: -- CHK_UNIQ_COMP_QUAL
5677: ,p_effective_date => p_effective_date
5678: );
5679:
5680: */
5681: hr_utility.set_location(l_proc, 80);
5682:
5683: --
5684: -- do the descriptive flex validation.
5685: --
5684: -- do the descriptive flex validation.
5685: --
5686: per_cel_bus.chk_df(p_rec => p_rec);
5687:
5688: hr_utility.set_location(l_proc, 80);
5689:
5690: --
5691: -- do the developer descriptive flex validation.
5692: --
5691: -- do the developer descriptive flex validation.
5692: --
5693: per_cel_bus.chk_ddf(p_rec => p_rec);
5694:
5695: hr_utility.set_location('Leaving:'||l_proc, 80);
5696: End update_validate;
5697: --
5698: -- ----------------------------------------------------------------------------
5699: -- |---------------------------< delete_validate >----------------------------|
5702: --
5703: l_proc varchar2(72) := g_package||'delete_validate';
5704: --
5705: Begin
5706: hr_utility.set_location('Entering:'||l_proc, 5);
5707: --
5708: -- Call all supporting business operations
5709: -- Business Rule Mapping
5710: -- =====================
5723: ,p_group_competence_type => per_cel_shd.g_old_rec.group_competence_type
5724: );
5725: --
5726:
5727: hr_utility.set_location(' Leaving:'||l_proc, 10);
5728: End delete_validate;
5729: --
5730: -- ----------------------------------------------------------------------------
5731: -- |-----------------------< return_legislation_code >-------------------------|
5747: l_legislation_code varchar2(150);
5748: l_business_group_flag varchar2(1);
5749: --
5750: Begin
5751: hr_utility.set_location('Entering:'||l_proc, 5);
5752: --
5753: -- Ensure that all the mandatory parameters are not null
5754: --
5755: hr_api.mandatory_arg_error (p_api_name => l_proc,
5772: -- call to this function. Just return the value in the global
5773: -- variable.
5774: --
5775: l_legislation_code := g_legislation_code;
5776: hr_utility.set_location(l_proc, 20);
5777: else
5778: --
5779: -- The ID is different to the last call to this function
5780: -- or this is the first call to this function.
5785: close csr_leg_code;
5786: --
5787: -- The primary key is invalid therefore we must error out
5788: --
5789: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
5790: hr_multi_message.add
5791: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ELEMENT_ID');
5792: hr_utility.raise_error;
5793: end if;
5788: --
5789: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
5790: hr_multi_message.add
5791: (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ELEMENT_ID');
5792: hr_utility.raise_error;
5793: end if;
5794: --
5795: close csr_leg_code;
5796: g_competence_element_id:= p_competence_element_id;
5797: g_legislation_code := l_legislation_code;
5798: end if;
5799: return l_legislation_code;
5800: --
5801: hr_utility.set_location(' Leaving:'||l_proc, 10);
5802: --
5803: End return_legislation_code;
5804: --
5805: --