28: l_argument varchar2(30);
29: --
30: Begin
31: --
32: hr_utility.set_location('Entering:'|| l_proc,5);
33: --
34: -- Only proceed with validation if a row exists for
35: -- the current record in the HR Schema.
36: if not per_asn_shd.api_updating
37: --
38: (p_assessment_id => p_rec.assessment_id
39: ,p_object_version_number => p_rec.object_version_number
40: ) then
41: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
42: hr_utility.set_message_token('PROCEDURE', l_proc);
43: hr_utility.set_message_token('STEP', '5');
44: end if;
45: --
38: (p_assessment_id => p_rec.assessment_id
39: ,p_object_version_number => p_rec.object_version_number
40: ) then
41: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
42: hr_utility.set_message_token('PROCEDURE', l_proc);
43: hr_utility.set_message_token('STEP', '5');
44: end if;
45: --
46: hr_utility.set_location (l_proc, 6);
39: ,p_object_version_number => p_rec.object_version_number
40: ) then
41: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
42: hr_utility.set_message_token('PROCEDURE', l_proc);
43: hr_utility.set_message_token('STEP', '5');
44: end if;
45: --
46: hr_utility.set_location (l_proc, 6);
47: --
42: hr_utility.set_message_token('PROCEDURE', l_proc);
43: hr_utility.set_message_token('STEP', '5');
44: end if;
45: --
46: hr_utility.set_location (l_proc, 6);
47: --
48: if p_rec.business_group_id <> per_asn_shd.g_old_rec.business_group_id then
49: l_argument := 'business_group_id';
50: raise l_error;
68: ,p_base_table => per_asn_shd.g_tab_nam);
69: when others then
70: raise;
71: --
72: hr_utility.set_location(' Leaving : '|| l_proc, 10);
73: --
74: end chk_non_updateable_args;
75: --
76: -- ----------------------------------------------------------------------------
133: l_ast_date_from per_assessment_types.date_from%TYPE;
134: l_ast_date_to per_assessment_types.date_to%TYPE;
135: --
136: begin
137: hr_utility.set_location('Entering:'|| l_proc, 1);
138: hr_utility.set_location('assessment_type_id = '|| p_assessment_type_id,1);
139: --
140: -- Processing continues if :
141: -- a) The row is being inserted.
134: l_ast_date_to per_assessment_types.date_to%TYPE;
135: --
136: begin
137: hr_utility.set_location('Entering:'|| l_proc, 1);
138: hr_utility.set_location('assessment_type_id = '|| p_assessment_type_id,1);
139: --
140: -- Processing continues if :
141: -- a) The row is being inserted.
142: -- b) The row is being updated and
161: --
162: if csr_ass_used_in_comp%found then
163: --
164: close csr_ass_used_in_comp;
165: hr_utility.set_message(801, 'HR_51582_ASN_USED_IN_COMP_ELE');
166: hr_utility.raise_error;
167: --
168: end if;
169: --
162: if csr_ass_used_in_comp%found then
163: --
164: close csr_ass_used_in_comp;
165: hr_utility.set_message(801, 'HR_51582_ASN_USED_IN_COMP_ELE');
166: hr_utility.raise_error;
167: --
168: end if;
169: --
170: close csr_ass_used_in_comp;
192: -- For insert and update, check whether the assessment_type is active
193: --
194: if (((p_assessment_date < l_ast_date_from) and (l_ast_date_from is not null)) or ((p_assessment_date > l_ast_date_to) and (l_ast_date_to is not null))) then
195: --
196: hr_utility.set_message(801, 'HR_51584_ASN_AST_IS_INACTIVE');
197: hr_utility.raise_error;
198: --
199: end if;
200: --
193: --
194: if (((p_assessment_date < l_ast_date_from) and (l_ast_date_from is not null)) or ((p_assessment_date > l_ast_date_to) and (l_ast_date_to is not null))) then
195: --
196: hr_utility.set_message(801, 'HR_51584_ASN_AST_IS_INACTIVE');
197: hr_utility.raise_error;
198: --
199: end if;
200: --
201: end if;
199: end if;
200: --
201: end if;
202: --
203: hr_utility.set_location('Leaving:'|| l_proc, 1);
204:
205: EXCEPTION
206:
207: when app_exception.application_exception then
244: l_api_updating boolean;
245: l_proc varchar2(72):=g_package||'chk_assessment_date';
246: --
247: begin
248: hr_utility.set_location('Entering:'|| l_proc, 1);
249: l_api_updating := per_asn_shd.api_updating
250: (p_assessment_id => p_assessment_id
251: ,p_object_version_number => p_object_version_number
252: );
257: <> nvl(p_assessment_date, hr_api.g_date))
258: or (not l_api_updating)) then
259:
260: if (p_assessment_date is NULL) then
261: hr_utility.set_message(801, 'HR_51784_ASN_DATE_NULL');
262: hr_utility.raise_error;
263: end if;
264: end if;
265: hr_utility.set_location('Leaving:'|| l_proc, 1);
258: or (not l_api_updating)) then
259:
260: if (p_assessment_date is NULL) then
261: hr_utility.set_message(801, 'HR_51784_ASN_DATE_NULL');
262: hr_utility.raise_error;
263: end if;
264: end if;
265: hr_utility.set_location('Leaving:'|| l_proc, 1);
266:
261: hr_utility.set_message(801, 'HR_51784_ASN_DATE_NULL');
262: hr_utility.raise_error;
263: end if;
264: end if;
265: hr_utility.set_location('Leaving:'|| l_proc, 1);
266:
267: EXCEPTION
268:
269: when app_exception.application_exception then
322: --
323: l_api_updating boolean;
324: --
325: begin
326: hr_utility.set_location('Entering:'|| l_proc, 1);
327: --
328:
329: open csr_chk_person_sta_date;
330: fetch csr_chk_person_sta_date into l_ASN_PERS_STA_DATE, l_ASN_PERS_BG;
334: close csr_chk_person_sta_date;
335: --
336: -- raise an error as the person_id doesn't exist
337: --
338: hr_utility.set_message(801, 'HR_51586_ASN_PER_NOT_EXIST');
339: hr_utility.raise_error;
340: --
341: end if;
342: close csr_chk_person_sta_date;
335: --
336: -- raise an error as the person_id doesn't exist
337: --
338: hr_utility.set_message(801, 'HR_51586_ASN_PER_NOT_EXIST');
339: hr_utility.raise_error;
340: --
341: end if;
342: close csr_chk_person_sta_date;
343: --
346: if (l_ASN_PERS_BG <> p_business_group_id) then
347: --
348: -- raise an error as the person is in the wrong business_group
349: --
350: hr_utility.set_message(801, 'HR_51806_ASN_PER_NOT_BG');
351: hr_utility.raise_error;
352: --
353: end if;
354: --
347: --
348: -- raise an error as the person is in the wrong business_group
349: --
350: hr_utility.set_message(801, 'HR_51806_ASN_PER_NOT_BG');
351: hr_utility.raise_error;
352: --
353: end if;
354: --
355: -- The assessment_date has to be on or after the person start date
355: -- The assessment_date has to be on or after the person start date
356: --
357: if (p_assessment_date < l_ASN_PERS_STA_DATE) then
358: --
359: hr_utility.set_message(801, 'HR_51587_ASN_PER_NOT_EXIST_DA');
360: hr_utility.raise_error;
361: --
362: end if;
363: --
356: --
357: if (p_assessment_date < l_ASN_PERS_STA_DATE) then
358: --
359: hr_utility.set_message(801, 'HR_51587_ASN_PER_NOT_EXIST_DA');
360: hr_utility.raise_error;
361: --
362: end if;
363: --
364: hr_utility.set_location('Leaving:'|| l_proc, 1);
360: hr_utility.raise_error;
361: --
362: end if;
363: --
364: hr_utility.set_location('Leaving:'|| l_proc, 1);
365:
366: EXCEPTION
367:
368: when app_exception.application_exception then
421: l_ASN_ASSPERS_STA_DATE per_people_f.start_date%TYPE;
422: l_ASN_ASSPERS_BG per_people_f.business_group_id%TYPE;
423: --
424: begin
425: hr_utility.set_location('Entering:'|| l_proc, 1);
426:
427: --
428: -- Tests are carried out on insert, and update (even if values haven't changed)
429: -- as data in the referenced table may have.
436: close csr_chk_assessper_sta_date;
437: --
438: -- raise an error as the person_id doesn't exist
439: --
440: hr_utility.set_message(801, 'HR_51588_ASN_ASSPER_NOT_EXIST');
441: hr_utility.raise_error;
442: --
443: end if;
444: close csr_chk_assessper_sta_date;
437: --
438: -- raise an error as the person_id doesn't exist
439: --
440: hr_utility.set_message(801, 'HR_51588_ASN_ASSPER_NOT_EXIST');
441: hr_utility.raise_error;
442: --
443: end if;
444: close csr_chk_assessper_sta_date;
445: --
454: if (l_ASN_ASSPERS_BG <> p_business_group_id) then
455: --
456: -- raise an error as the person is in the wrong business_group
457: --
458: hr_utility.set_message(801, 'HR_51808_ASN_ASSPER_NOT_BG');
459: hr_utility.raise_error;
460: --
461: end if;
462: end if;
455: --
456: -- raise an error as the person is in the wrong business_group
457: --
458: hr_utility.set_message(801, 'HR_51808_ASN_ASSPER_NOT_BG');
459: hr_utility.raise_error;
460: --
461: end if;
462: end if;
463: -- bug 1980440 fix ends
465: -- The assessment_date has to be on or after the assessors start date
466: --
467: if (p_assessment_date < l_ASN_ASSPERS_STA_DATE) then
468: --
469: hr_utility.set_message(801, 'HR_51589_ASN_ASSPER_NO_XIST_DA');
470: hr_utility.raise_error;
471: --
472: end if;
473: --
466: --
467: if (p_assessment_date < l_ASN_ASSPERS_STA_DATE) then
468: --
469: hr_utility.set_message(801, 'HR_51589_ASN_ASSPER_NO_XIST_DA');
470: hr_utility.raise_error;
471: --
472: end if;
473: --
474: hr_utility.set_location('Leaving:'|| l_proc, 1);
470: hr_utility.raise_error;
471: --
472: end if;
473: --
474: hr_utility.set_location('Leaving:'|| l_proc, 1);
475: --
476: EXCEPTION
477:
478: when app_exception.application_exception then
519: l_proc varchar2(72):=g_package||'chk_group_date_id';
520: --
521: --
522: begin
523: hr_utility.set_location('Entering:'|| l_proc, 1);
524: --
525: -- Tests are carried out on insert only.
526: --
527: --
539: (p_group_initiator_id is null And p_group_date is not null)) Then
540: --
541: -- raise an error as the either both should exist or neither should.
542: --
543: hr_utility.set_message(801, 'HR_52308_CM_GPR_DATE_ID_PROB');
544: hr_utility.raise_error;
545: --
546: end if;
547: --
540: --
541: -- raise an error as the either both should exist or neither should.
542: --
543: hr_utility.set_message(801, 'HR_52308_CM_GPR_DATE_ID_PROB');
544: hr_utility.raise_error;
545: --
546: end if;
547: --
548: end if;
545: --
546: end if;
547: --
548: end if;
549: hr_utility.set_location('Leaving:'|| l_proc, 2);
550: --
551: EXCEPTION
552:
553: when app_exception.application_exception then
606: l_asn_grp_pers_sta_date per_people_f.start_date%TYPE;
607: l_asn_grp_pers_bg per_people_f.business_group_id%TYPE;
608: --
609: begin
610: hr_utility.set_location('Entering:'|| l_proc, 1);
611: --
612: l_api_updating := per_asn_shd.api_updating
613: (p_assessment_id => p_assessment_id
614: ,p_object_version_number => p_object_version_number
632: close csr_chk_grp_per_sta_date;
633: --
634: -- raise an error as the person_id doesn't exist
635: --
636: hr_utility.set_message(801, 'HR_52305_ASN_GRPPER_NOT_EXIST');
637: hr_utility.raise_error;
638: --
639: end if;
640: close csr_chk_grp_per_sta_date;
633: --
634: -- raise an error as the person_id doesn't exist
635: --
636: hr_utility.set_message(801, 'HR_52305_ASN_GRPPER_NOT_EXIST');
637: hr_utility.raise_error;
638: --
639: end if;
640: close csr_chk_grp_per_sta_date;
641: --
644: IF (l_asn_grp_pers_bg <> p_business_group_id) THEN
645: --
646: -- raise an error as the person is in the wrong business_group
647: --
648: hr_utility.set_message(801, 'HR_52306_ASN_GRPPER_NOT_BG');
649: hr_utility.raise_error;
650: --
651: END IF;
652: --
645: --
646: -- raise an error as the person is in the wrong business_group
647: --
648: hr_utility.set_message(801, 'HR_52306_ASN_GRPPER_NOT_BG');
649: hr_utility.raise_error;
650: --
651: END IF;
652: --
653: -- The group_date has to be on or after the group initiators start date
653: -- The group_date has to be on or after the group initiators start date
654: --
655: IF (p_group_date < l_asn_grp_pers_sta_date) then
656: --
657: hr_utility.set_message(801, 'HR_52307_ASN_GRPPER_NO_XIST_DA');
658: hr_utility.raise_error;
659: --
660: END IF;
661: --
654: --
655: IF (p_group_date < l_asn_grp_pers_sta_date) then
656: --
657: hr_utility.set_message(801, 'HR_52307_ASN_GRPPER_NO_XIST_DA');
658: hr_utility.raise_error;
659: --
660: END IF;
661: --
662: END IF;
661: --
662: END IF;
663: end if;
664: --
665: hr_utility.set_location('Leaving:'|| l_proc, 1);
666: --
667: EXCEPTION
668:
669: when app_exception.application_exception then
709: l_api_updating boolean;
710: l_proc varchar2(72):=g_package||'chk_status';
711: --
712: begin
713: hr_utility.set_location('Entering:'|| l_proc, 1);
714: l_api_updating := per_asn_shd.api_updating
715: (p_assessment_id => p_assessment_id
716: ,p_object_version_number => p_object_version_number
717: );
723: --
724: -- Check that the value in p_status exist in hr_lookups
725: --
726: if p_status is not Null Then
727: hr_utility.set_location(l_proc||':Value>'||p_status, 5);
728: if hr_api.not_exists_in_hr_lookups
729: (p_effective_date => p_effective_date
730: ,p_lookup_type => 'APPRAISAL_ASSESSMENT_STATUS'
731: ,p_lookup_code => p_status
729: (p_effective_date => p_effective_date
730: ,p_lookup_type => 'APPRAISAL_ASSESSMENT_STATUS'
731: ,p_lookup_code => p_status
732: ) then
733: hr_utility.set_location(l_proc, 10);
734: hr_utility.set_message(801,'HR_51585_ASN_COMPLETE_INVAL');
735: hr_utility.raise_error;
736: end if;
737: end if;
730: ,p_lookup_type => 'APPRAISAL_ASSESSMENT_STATUS'
731: ,p_lookup_code => p_status
732: ) then
733: hr_utility.set_location(l_proc, 10);
734: hr_utility.set_message(801,'HR_51585_ASN_COMPLETE_INVAL');
735: hr_utility.raise_error;
736: end if;
737: end if;
738: end if;
731: ,p_lookup_code => p_status
732: ) then
733: hr_utility.set_location(l_proc, 10);
734: hr_utility.set_message(801,'HR_51585_ASN_COMPLETE_INVAL');
735: hr_utility.raise_error;
736: end if;
737: end if;
738: end if;
739: --
736: end if;
737: end if;
738: end if;
739: --
740: hr_utility.set_location('Leaving:'|| l_proc, 100);
741: EXCEPTION
742:
743: when app_exception.application_exception then
744: if hr_multi_message.exception_add
785: l_api_updating boolean;
786: l_proc varchar2(72):=g_package||'chk_assessment_period';
787: --
788: begin
789: hr_utility.set_location('Entering:'|| l_proc, 1);
790: l_api_updating := per_asn_shd.api_updating
791: (p_assessment_id => p_assessment_id
792: ,p_object_version_number => p_object_version_number
793: );
895: --
896: l_exists varchar2(1);
897: --
898: begin
899: hr_utility.set_location('Entering:'|| l_proc, 1);
900: --
901: -- always check ins upd even if values not changed
902:
903:
908: open csr_duplicate_rows;
909: fetch csr_duplicate_rows into l_exists;
910: --
911: if csr_duplicate_rows%found then
912: hr_utility.set_location('Dup.found:'|| l_proc, 3);
913: close csr_duplicate_rows;
914: --
915: per_asn_shd.constraint_error
916: (p_constraint_name => 'PER_ASSESSMENTS_UK1');
976:
977: l_exists varchar2(1);
978:
979: begin
980: hr_utility.set_location('Entering:'|| l_proc, 1);
981: l_api_updating := per_asn_shd.api_updating
982: (p_assessment_id => p_assessment_id
983: ,p_object_version_number => p_object_version_number
984: );
988: or (not l_api_updating)) then
989:
990: --
991: if (p_assessment_group_id is not null) then
992: hr_utility.set_location('assessment_group_id must be NULL:'|| l_proc, 1);
993: --
994: open csr_chk_ass_group_id;
995: fetch csr_chk_ass_group_id into l_exists;
996: --
995: fetch csr_chk_ass_group_id into l_exists;
996: --
997: -- If the group isn't in the referenced table, raise an error
998: --
999: hr_utility.set_location('assessment_group_id :'|| p_assessment_group_id, 1);
1000: if (csr_chk_ass_group_id%notfound) then
1001: close csr_chk_ass_group_id;
1002: --
1003: per_asn_shd.constraint_error
1062:
1063: l_exists varchar2(1);
1064:
1065: begin
1066: hr_utility.set_location('Entering:'|| l_proc, 1);
1067: l_api_updating := per_asn_shd.api_updating
1068: (p_assessment_id => p_assessment_id
1069: ,p_object_version_number => p_object_version_number
1070: );
1134: --
1135: l_exists varchar2(1);
1136: --
1137: begin
1138: hr_utility.set_location('Entering:'|| l_proc, 1);
1139: --
1140: -- Check that the assessment is not referenced by a competence element
1141: --
1142: open csr_comp_elements_usage;
1143: fetch csr_comp_elements_usage into l_exists;
1144: if csr_comp_elements_usage%found then
1145: close csr_comp_elements_usage;
1146: --
1147: hr_utility.set_location(l_proc,5);
1148: hr_utility.set_message (801, 'HR_51812_ASN_REF_BY_COMP');
1149: hr_utility.raise_error;
1150: --
1151: end if;
1144: if csr_comp_elements_usage%found then
1145: close csr_comp_elements_usage;
1146: --
1147: hr_utility.set_location(l_proc,5);
1148: hr_utility.set_message (801, 'HR_51812_ASN_REF_BY_COMP');
1149: hr_utility.raise_error;
1150: --
1151: end if;
1152: close csr_comp_elements_usage;
1145: close csr_comp_elements_usage;
1146: --
1147: hr_utility.set_location(l_proc,5);
1148: hr_utility.set_message (801, 'HR_51812_ASN_REF_BY_COMP');
1149: hr_utility.raise_error;
1150: --
1151: end if;
1152: close csr_comp_elements_usage;
1153: --
1193: --
1194: l_proc varchar2(72) := g_package||'chk_df';
1195: --
1196: begin
1197: hr_utility.set_location('Entering:'||l_proc, 10);
1198: --
1199: if ((p_rec.assessment_id is not null) and (
1200: nvl(per_asn_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1201: nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1293: ,p_attribute20_value => p_rec.attribute20
1294: );
1295: end if;
1296: --
1297: hr_utility.set_location(' Leaving:'||l_proc, 20);
1298:
1299: end chk_df;
1300: --
1301: -- ----------------------------------------------------------------------------
1307: --
1308: l_proc varchar2(72) := g_package||'insert_validate';
1309: --
1310: Begin
1311: hr_utility.set_location('Entering:'||l_proc, 5);
1312: --
1313: -- Call all supporting business operations. Mapping to the appropiate
1314: -- Business Rules in perasn.bru is provided.
1315: --
1459: -- Call descriptive flexfield validation routines
1460: --
1461: per_asn_bus.chk_df(p_rec => p_rec);
1462: --
1463: hr_utility.set_location(' Leaving:'||l_proc, 10);
1464: End insert_validate;
1465: --
1466: -- ----------------------------------------------------------------------------
1467: -- |---------------------------< update_validate >----------------------------|
1472: --
1473: l_proc varchar2(72) := g_package||'update_validate';
1474: --
1475: Begin
1476: hr_utility.set_location('Entering:'||l_proc, 5);
1477: --
1478: -- Call all supporting business operations. Mapping to the appropiate
1479: -- business rules provided.
1480: --
1574: -- Call descriptive flexfield validation routines
1575: --
1576: per_asn_bus.chk_df(p_rec => p_rec);
1577: --
1578: hr_utility.set_location(' Leaving:'||l_proc, 10);
1579: End update_validate;
1580: --
1581: -- ----------------------------------------------------------------------------
1582: -- |---------------------------< delete_validate >----------------------------|
1585: --
1586: l_proc varchar2(72) := g_package||'delete_validate';
1587: --
1588: Begin
1589: hr_utility.set_location('Entering:'||l_proc, 5);
1590: --
1591: -- Call all supporting business operations and show mapping
1592: --
1593: per_asn_bus.chk_comp_elements
1594: (p_assessment_id => p_rec.assessment_id
1595: ,p_object_version_number => p_rec.object_version_number
1596: );
1597: --
1598: hr_utility.set_location(' Leaving:'||l_proc, 10);
1599: End delete_validate;
1600: --
1601: -- ----------------------------------------------------------------------------
1602: -- |-----------------------< return_legislation_code >-------------------------|
1617: l_proc varchar2(72) := g_package||'return_legislation_code';
1618: l_legislation_code varchar2(150);
1619: --
1620: Begin
1621: hr_utility.set_location('Entering:'||l_proc, 5);
1622: --
1623: -- Ensure that all the mandatory parameters are not null
1624: --
1625: hr_api.mandatory_arg_error (p_api_name => l_proc,
1632: -- call to this function. Just return the value in the global
1633: -- variable.
1634: --
1635: l_legislation_code := g_legislation_code;
1636: hr_utility.set_location(l_proc, 15);
1637: else
1638: --
1639: -- The ID is different to the last call to this function
1640: -- or this is the first call to this function.
1645: close csr_leg_code;
1646: --
1647: -- The primary key is invalid therefore we must error out
1648: --
1649: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1650: hr_utility.raise_error;
1651: end if;
1652: --
1653: close csr_leg_code;
1646: --
1647: -- The primary key is invalid therefore we must error out
1648: --
1649: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1650: hr_utility.raise_error;
1651: end if;
1652: --
1653: close csr_leg_code;
1654: --
1657: end if;
1658: --
1659: return l_legislation_code;
1660: --
1661: hr_utility.set_location(' Leaving:'||l_proc, 20);
1662: --
1663: End return_legislation_code;
1664: --
1665: --