[Home] [Help]
176: l_future_asgs_count INTEGER := 0;
177: --
178: BEGIN
179: --
180: hr_utility.set_location('Entering:'||l_proc,10);
181: --
182: FOR l_future_asgs_rec IN
183: csr_future_asgs
184: (p_person_id => p_person_id
188: LOOP
189: l_future_asgs_count := l_future_asgs_count + 1;
190: END LOOP;
191: --
192: hr_utility.set_location(' Leaving:'||l_proc,100);
193: --
194: RETURN(l_future_asgs_count);
195: --
196: EXCEPTION
248: l_nonaccepted_asgs_count INTEGER := 0;
249: --
250: BEGIN
251: --
252: hr_utility.set_location('Entering:'||l_proc,10);
253: --
254: FOR l_nonaccepted_asgs_rec IN
255: csr_nonaccepted_asgs
256: (p_person_id => p_person_id
259: LOOP
260: l_nonaccepted_asgs_count := l_nonaccepted_asgs_count + 1;
261: END LOOP;
262: --
263: hr_utility.set_location(' Leaving:'||l_proc,100);
264: --
265: RETURN(l_nonaccepted_asgs_count);
266: --
267: EXCEPTION
319: l_accepted_asgs_count INTEGER := 0;
320: --
321: BEGIN
322: --
323: hr_utility.set_location('Entering:'||l_proc,10);
324: --
325: FOR l_accepted_asgs_rec IN
326: csr_accepted_asgs
327: (p_person_id => p_person_id
330: LOOP
331: l_accepted_asgs_count := l_accepted_asgs_count + 1;
332: END LOOP;
333: --
334: hr_utility.set_location(' Leaving:'||l_proc,100);
335: --
336: RETURN(l_accepted_asgs_count);
337: --
338: EXCEPTION
390: l_primary_asgs_count INTEGER := 0;
391: --
392: BEGIN
393: --
394: hr_utility.set_location('Entering:'||l_proc,10);
395: --
396: FOR l_primary_asgs_rec IN
397: csr_primary_asgs
398: (p_person_id => p_person_id
401: LOOP
402: l_primary_asgs_count := l_primary_asgs_count + 1;
403: END LOOP;
404: --
405: hr_utility.set_location(' Leaving:'||l_proc,100);
406: --
407: RETURN(l_primary_asgs_count);
408: --
409: EXCEPTION
459: l_per_details_rec csr_per_details%ROWTYPE;
460: --
461: BEGIN
462: --
463: hr_utility.set_location('Entering:'||l_proc,10);
464: --
465: OPEN csr_per_details
466: (p_person_id => p_person_id
467: ,p_effective_date => p_effective_date
468: );
469: FETCH csr_per_details INTO l_per_details_rec;
470: IF csr_per_details%NOTFOUND
471: THEN
472: hr_utility.set_location(l_proc,20);
473: CLOSE csr_per_details;
474: hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
475: hr_utility.raise_error;
476: END IF;
470: IF csr_per_details%NOTFOUND
471: THEN
472: hr_utility.set_location(l_proc,20);
473: CLOSE csr_per_details;
474: hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
475: hr_utility.raise_error;
476: END IF;
477: CLOSE csr_per_details;
478: --
471: THEN
472: hr_utility.set_location(l_proc,20);
473: CLOSE csr_per_details;
474: hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
475: hr_utility.raise_error;
476: END IF;
477: CLOSE csr_per_details;
478: --
479: hr_utility.set_location(' Leaving:'||l_proc,100);
475: hr_utility.raise_error;
476: END IF;
477: CLOSE csr_per_details;
478: --
479: hr_utility.set_location(' Leaving:'||l_proc,100);
480: --
481: RETURN(l_per_details_rec);
482: --
483: EXCEPTION
532: l_apl_details_rec csr_apl_details%ROWTYPE;
533: --
534: BEGIN
535: --
536: hr_utility.set_location('Entering:'||l_proc,10);
537: --
538: OPEN csr_apl_details
539: (p_person_id => p_person_id
540: ,p_effective_date => p_effective_date
541: );
542: FETCH csr_apl_details INTO l_apl_details_rec;
543: CLOSE csr_apl_details;
544: --
545: hr_utility.set_location(' Leaving:'||l_proc,100);
546: --
547: RETURN(l_apl_details_rec);
548: --
549: EXCEPTION
598: l_pds_details_rec csr_pds_details%ROWTYPE;
599: --
600: BEGIN
601: --
602: hr_utility.set_location('Entering:'||l_proc,10);
603: --
604: OPEN csr_pds_details
605: (p_person_id => p_person_id
606: ,p_effective_date => p_effective_date
607: );
608: FETCH csr_pds_details INTO l_pds_details_rec;
609: CLOSE csr_pds_details;
610: --
611: hr_utility.set_location(' Leaving:'||l_proc,100);
612: --
613: RETURN(l_pds_details_rec);
614: --
615: EXCEPTION
655: l_oversubscribed_vacancy_id number;
656: --
657: BEGIN
658: --
659: hr_utility.set_location('Entering:'||l_proc,10);
660: --
661: l_per_object_version_number:= p_per_object_version_number;
662: l_employee_number:= p_employee_number;
663: --
681: p_per_effective_start_date:=l_per_effective_start_date;
682: p_per_effective_end_date:=l_per_effective_end_date;
683: p_assign_payroll_warning:=l_assign_payroll_warning;
684: --
685: hr_utility.set_location('Leaving:'||l_proc,20);
686: --
687: end hire_to_employee_applicant;
688: --
689: -- -----------------------------------------------------------------------------
720: l_oversubscribed_vacancy_id number;
721: --
722: BEGIN
723: --
724: hr_utility.set_location('Entering:'||l_proc,10);
725: --
726: l_per_object_version_number:= p_per_object_version_number;
727: l_employee_number:= p_employee_number;
728: --
746: p_per_effective_start_date:=l_per_effective_start_date;
747: p_per_effective_end_date:=l_per_effective_end_date;
748: p_assign_payroll_warning:=l_assign_payroll_warning;
749: --
750: hr_utility.set_location('Leaving:'||l_proc,20);
751: --
752: end hire_to_employee_applicant;
753: --
754: -- -----------------------------------------------------------------------------
846: and effective_start_date >= p_effective_date);
847: --
848: BEGIN
849: --
850: hr_utility.set_location('Entering:'||l_proc,10);
851: --
852: -- Ensure mandatory arguments have been passed
853: --
854: hr_api.mandatory_arg_error
870: -- Issue savepoint
871: --
872: SAVEPOINT hire_to_employee_applicant;
873: --
874: hr_utility.set_location(l_proc,20);
875: --
876: -- Get the derived details for the person on the hire date
877: --
878: l_per_details_rec := per_details
879: (p_person_id => p_person_id
880: ,p_effective_date => l_hire_date
881: );
882: --
883: hr_utility.set_location(l_proc,40);
884: --
885: -- 2264191 changed behaviour: single asg hire requires the assignment id.
886: -- only hire all accepted if passed the 'Y' flag, not for any other value
887: -- 'Y' replicates the old behaviour
889: IF p_hire_all_accepted_asgs is null
890: OR ( p_hire_all_accepted_asgs <> 'Y'
891: AND p_hire_all_accepted_asgs is not null)
892: THEN
893: hr_utility.set_location(l_proc,45);
894: l_hire_all_accepted_asgs := 'N';
895: l_hire_single_asg_id := p_assignment_id;
896: hr_api.mandatory_arg_error
897: (p_api_name => l_proc
924: ,p_hook_type => 'BP'
925: );
926: END;
927: --
928: hr_utility.set_location(l_proc,50);
929: --
930: -- Check the person is of a correct system person type
931: --
932: IF l_per_details_rec.system_person_type NOT IN
931: --
932: IF l_per_details_rec.system_person_type NOT IN
933: ('APL','EMP_APL','EX_EMP_APL','APL_EX_APL')
934: THEN
935: hr_utility.set_location(l_proc,60);
936: hr_utility.set_message(800,'PER_52096_APL_INV_PERSON_TYPE');
937: hr_utility.raise_error;
938: END IF;
939: --
932: IF l_per_details_rec.system_person_type NOT IN
933: ('APL','EMP_APL','EX_EMP_APL','APL_EX_APL')
934: THEN
935: hr_utility.set_location(l_proc,60);
936: hr_utility.set_message(800,'PER_52096_APL_INV_PERSON_TYPE');
937: hr_utility.raise_error;
938: END IF;
939: --
940: hr_utility.set_location(l_proc,70);
933: ('APL','EMP_APL','EX_EMP_APL','APL_EX_APL')
934: THEN
935: hr_utility.set_location(l_proc,60);
936: hr_utility.set_message(800,'PER_52096_APL_INV_PERSON_TYPE');
937: hr_utility.raise_error;
938: END IF;
939: --
940: hr_utility.set_location(l_proc,70);
941: --
936: hr_utility.set_message(800,'PER_52096_APL_INV_PERSON_TYPE');
937: hr_utility.raise_error;
938: END IF;
939: --
940: hr_utility.set_location(l_proc,70);
941: --
942: -- Ensure the employee number will not be changed if it exists
943: --
944: IF l_per_details_rec.employee_number IS NOT NULL
943: --
944: IF l_per_details_rec.employee_number IS NOT NULL
945: AND NVL(p_employee_number,hr_api.g_number) <> l_per_details_rec.employee_number
946: THEN
947: hr_utility.set_location(l_proc,80);
948: p_employee_number := l_per_details_rec.employee_number;
949: END IF;
950: --
951: hr_utility.set_location(l_proc,90);
947: hr_utility.set_location(l_proc,80);
948: p_employee_number := l_per_details_rec.employee_number;
949: END IF;
950: --
951: hr_utility.set_location(l_proc,90);
952: --
953: -- Check the person does not have future assignment changes
954: --
955: IF l_hire_all_accepted_asgs = 'Y' then
965: );
966: END IF;
967: IF l_future_asgs_count > 0
968: THEN
969: hr_utility.set_location(l_proc,100);
970: hr_utility.set_message(800,'HR_7975_ASG_INV_FUTURE_ASA');
971: hr_utility.raise_error;
972: END IF;
973: --
966: END IF;
967: IF l_future_asgs_count > 0
968: THEN
969: hr_utility.set_location(l_proc,100);
970: hr_utility.set_message(800,'HR_7975_ASG_INV_FUTURE_ASA');
971: hr_utility.raise_error;
972: END IF;
973: --
974: hr_utility.set_location(l_proc,110);
967: IF l_future_asgs_count > 0
968: THEN
969: hr_utility.set_location(l_proc,100);
970: hr_utility.set_message(800,'HR_7975_ASG_INV_FUTURE_ASA');
971: hr_utility.raise_error;
972: END IF;
973: --
974: hr_utility.set_location(l_proc,110);
975: --
970: hr_utility.set_message(800,'HR_7975_ASG_INV_FUTURE_ASA');
971: hr_utility.raise_error;
972: END IF;
973: --
974: hr_utility.set_location(l_proc,110);
975: --
976: -- If person type id is not null check it corresponds to type EMP_APL is
977: -- currently active and in the correct business group, otherwise set person
978: -- type id to the active default for EMP_APL in the correct business group
986: ,p_business_group_id => l_per_details_rec.business_group_id
987: ,p_expected_sys_type => l_system_person_type
988: );
989: */ --
990: hr_utility.set_location(l_proc,120);
991: --
992: -- 2264191 changed behaviour: If hiring all accepted, ensure there are some nonaccepted
993: -- applicant assignments that will remain for the person to be an applicant for.
994: --
998: );
999: IF l_nonaccepted_asgs_count = 0
1000: AND l_hire_all_accepted_asgs = 'Y'
1001: THEN
1002: hr_utility.set_location(l_proc,130);
1003: hr_utility.set_message(800,'HR_289149_NOHIR_NO_UNACCEPTED'); --'PER_52098_APL_INV_ASG_STATUS');
1004: hr_utility.raise_error;
1005: END IF;
1006: --
999: IF l_nonaccepted_asgs_count = 0
1000: AND l_hire_all_accepted_asgs = 'Y'
1001: THEN
1002: hr_utility.set_location(l_proc,130);
1003: hr_utility.set_message(800,'HR_289149_NOHIR_NO_UNACCEPTED'); --'PER_52098_APL_INV_ASG_STATUS');
1004: hr_utility.raise_error;
1005: END IF;
1006: --
1007: hr_utility.set_location(l_proc,140);
1000: AND l_hire_all_accepted_asgs = 'Y'
1001: THEN
1002: hr_utility.set_location(l_proc,130);
1003: hr_utility.set_message(800,'HR_289149_NOHIR_NO_UNACCEPTED'); --'PER_52098_APL_INV_ASG_STATUS');
1004: hr_utility.raise_error;
1005: END IF;
1006: --
1007: hr_utility.set_location(l_proc,140);
1008: --
1003: hr_utility.set_message(800,'HR_289149_NOHIR_NO_UNACCEPTED'); --'PER_52098_APL_INV_ASG_STATUS');
1004: hr_utility.raise_error;
1005: END IF;
1006: --
1007: hr_utility.set_location(l_proc,140);
1008: --
1009: -- Ensure there are some accepted applicant assignments for the person to be
1010: -- hired into (applies pre and post 2264191)
1011: --
1014: ,p_effective_date => l_hire_date
1015: );
1016: IF l_accepted_asgs_count = 0
1017: THEN
1018: hr_utility.set_location(l_proc,150);
1019: hr_utility.set_message(800,'HR_289150_NOHIR_NO_ACCEPTED'); --'PER_52098_APL_INV_ASG_STATUS');
1020: hr_utility.raise_error;
1021: END IF;
1022: --
1015: );
1016: IF l_accepted_asgs_count = 0
1017: THEN
1018: hr_utility.set_location(l_proc,150);
1019: hr_utility.set_message(800,'HR_289150_NOHIR_NO_ACCEPTED'); --'PER_52098_APL_INV_ASG_STATUS');
1020: hr_utility.raise_error;
1021: END IF;
1022: --
1023: -- 2264191 changed behaviour: If hiring a single assignment
1016: IF l_accepted_asgs_count = 0
1017: THEN
1018: hr_utility.set_location(l_proc,150);
1019: hr_utility.set_message(800,'HR_289150_NOHIR_NO_ACCEPTED'); --'PER_52098_APL_INV_ASG_STATUS');
1020: hr_utility.raise_error;
1021: END IF;
1022: --
1023: -- 2264191 changed behaviour: If hiring a single assignment
1024: -- ensure that there is more than one APL assignment to retain
1025: --
1026: IF l_hire_all_accepted_asgs = 'N'
1027: AND l_accepted_asgs_count + l_nonaccepted_asgs_count < 2
1028: THEN
1029: hr_utility.set_location(l_proc,155);
1030: hr_utility.set_message(800,'HR_289151_NOHIR_SGL_NO_RETAIN');
1031: hr_utility.raise_error;
1032: END IF;
1033: --
1026: IF l_hire_all_accepted_asgs = 'N'
1027: AND l_accepted_asgs_count + l_nonaccepted_asgs_count < 2
1028: THEN
1029: hr_utility.set_location(l_proc,155);
1030: hr_utility.set_message(800,'HR_289151_NOHIR_SGL_NO_RETAIN');
1031: hr_utility.raise_error;
1032: END IF;
1033: --
1034: hr_utility.set_location(l_proc,160);
1027: AND l_accepted_asgs_count + l_nonaccepted_asgs_count < 2
1028: THEN
1029: hr_utility.set_location(l_proc,155);
1030: hr_utility.set_message(800,'HR_289151_NOHIR_SGL_NO_RETAIN');
1031: hr_utility.raise_error;
1032: END IF;
1033: --
1034: hr_utility.set_location(l_proc,160);
1035: --
1030: hr_utility.set_message(800,'HR_289151_NOHIR_SGL_NO_RETAIN');
1031: hr_utility.raise_error;
1032: END IF;
1033: --
1034: hr_utility.set_location(l_proc,160);
1035: --
1036: -- 2264191 changed behaviour: Ensure the assignment id has been set
1037: -- if either no current primary employee
1038: -- assignment and multiple accepted applicant assignment exist, or if hiring a single
1045: );
1046: IF l_primary_asgs_count = 0
1047: AND l_hire_all_accepted_asgs = 'Y' --replicate the behaviour pre2264191
1048: THEN
1049: hr_utility.set_location(l_proc,170);
1050: IF p_assignment_id IS NULL
1051: THEN
1052: hr_utility.set_location(l_proc,180);
1053: IF l_accepted_asgs_count = 1
1048: THEN
1049: hr_utility.set_location(l_proc,170);
1050: IF p_assignment_id IS NULL
1051: THEN
1052: hr_utility.set_location(l_proc,180);
1053: IF l_accepted_asgs_count = 1
1054: THEN
1055: hr_utility.set_location(l_proc,190);
1056: OPEN csr_accepted_asgs
1051: THEN
1052: hr_utility.set_location(l_proc,180);
1053: IF l_accepted_asgs_count = 1
1054: THEN
1055: hr_utility.set_location(l_proc,190);
1056: OPEN csr_accepted_asgs
1057: (p_person_id => p_person_id
1058: ,p_effective_date => l_hire_date
1059: );
1060: FETCH csr_accepted_asgs INTO l_accepted_asgs_rec;
1061: CLOSE csr_accepted_asgs;
1062: l_new_primary_asg_id := l_accepted_asgs_rec.assignment_id;
1063: ELSE
1064: hr_utility.set_location(l_proc,200);
1065: hr_api.mandatory_arg_error
1066: (p_api_name => l_proc
1067: ,p_argument => 'assignment_id'
1068: ,p_argument_value => p_assignment_id);
1072: END IF;
1073: ELSIF l_primary_asgs_count = 0 --2264191 hiring single asg, must make it primary
1074: AND l_hire_all_accepted_asgs = 'N'
1075: THEN
1076: hr_utility.set_location(l_proc,205);
1077: l_new_primary_asg_id := p_assignment_id;
1078: ELSE -- a primary already exists
1079: hr_utility.set_location(l_proc,210);
1080: --IF p_assignment_id IS NOT NULL
1075: THEN
1076: hr_utility.set_location(l_proc,205);
1077: l_new_primary_asg_id := p_assignment_id;
1078: ELSE -- a primary already exists
1079: hr_utility.set_location(l_proc,210);
1080: --IF p_assignment_id IS NOT NULL
1081: --THEN
1082: hr_utility.set_location(l_proc,220);
1083: l_new_primary_asg_id := NULL;
1078: ELSE -- a primary already exists
1079: hr_utility.set_location(l_proc,210);
1080: --IF p_assignment_id IS NOT NULL
1081: --THEN
1082: hr_utility.set_location(l_proc,220);
1083: l_new_primary_asg_id := NULL;
1084: --END IF;
1085: END IF;
1086: --
1083: l_new_primary_asg_id := NULL;
1084: --END IF;
1085: END IF;
1086: --
1087: hr_utility.set_location(l_proc,230);
1088: --
1089: -- Ensure any assignment id specified is for the person, has an assignment
1090: -- type of A and a system status of ACCEPTED on the hire date
1091: --
1090: -- type of A and a system status of ACCEPTED on the hire date
1091: --
1092: IF p_assignment_id IS NOT NULL
1093: THEN
1094: hr_utility.set_location(l_proc,240);
1095: OPEN csr_accepted_asgs
1096: (p_person_id => p_person_id
1097: ,p_effective_date => l_hire_date
1098: ,p_assignment_id => p_assignment_id
1099: );
1100: FETCH csr_accepted_asgs INTO l_accepted_asgs_rec;
1101: IF csr_accepted_asgs%NOTFOUND
1102: THEN
1103: hr_utility.set_location(l_proc,250);
1104: CLOSE csr_accepted_asgs;
1105: hr_utility.set_message(800,'HR_289152_NOHIR_ACCEPT_APL_ASG'); --'PER_52099_ASG_INV_ASG_ID');
1106: hr_utility.raise_error;
1107: END IF;
1101: IF csr_accepted_asgs%NOTFOUND
1102: THEN
1103: hr_utility.set_location(l_proc,250);
1104: CLOSE csr_accepted_asgs;
1105: hr_utility.set_message(800,'HR_289152_NOHIR_ACCEPT_APL_ASG'); --'PER_52099_ASG_INV_ASG_ID');
1106: hr_utility.raise_error;
1107: END IF;
1108: CLOSE csr_accepted_asgs;
1109: END IF;
1102: THEN
1103: hr_utility.set_location(l_proc,250);
1104: CLOSE csr_accepted_asgs;
1105: hr_utility.set_message(800,'HR_289152_NOHIR_ACCEPT_APL_ASG'); --'PER_52099_ASG_INV_ASG_ID');
1106: hr_utility.raise_error;
1107: END IF;
1108: CLOSE csr_accepted_asgs;
1109: END IF;
1110: --
1107: END IF;
1108: CLOSE csr_accepted_asgs;
1109: END IF;
1110: --
1111: hr_utility.set_location(l_proc,260);
1112: --
1113: -- PTU : Changes
1114:
1115: l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
1120: -- Update the person details to the new person type, if it has changed
1121: --
1122: IF l_per_details_rec.person_type_id <> l_person_type_id1
1123: THEN
1124: hr_utility.set_location(l_proc,270);
1125: --2931560 added check for future person changes
1126: open csr_future_per_changes(l_hire_date);
1127: fetch csr_future_per_changes into l_dummy;
1128: if csr_future_per_changes%found then
1126: open csr_future_per_changes(l_hire_date);
1127: fetch csr_future_per_changes into l_dummy;
1128: if csr_future_per_changes%found then
1129: close csr_future_per_changes;
1130: -- hr_utility.set_message('PER','HR_289729_FUT_PER_NOHIR');
1131: hr_utility.set_message(800,'HR_289729_FUT_PER_NOHIR'); -- Bug 2931560
1132: hr_utility.raise_error;
1133: else
1134: close csr_future_per_changes;
1127: fetch csr_future_per_changes into l_dummy;
1128: if csr_future_per_changes%found then
1129: close csr_future_per_changes;
1130: -- hr_utility.set_message('PER','HR_289729_FUT_PER_NOHIR');
1131: hr_utility.set_message(800,'HR_289729_FUT_PER_NOHIR'); -- Bug 2931560
1132: hr_utility.raise_error;
1133: else
1134: close csr_future_per_changes;
1135: end if;
1128: if csr_future_per_changes%found then
1129: close csr_future_per_changes;
1130: -- hr_utility.set_message('PER','HR_289729_FUT_PER_NOHIR');
1131: hr_utility.set_message(800,'HR_289729_FUT_PER_NOHIR'); -- Bug 2931560
1132: hr_utility.raise_error;
1133: else
1134: close csr_future_per_changes;
1135: end if;
1136: --
1155: ,p_orig_hire_warning => l_orig_hire_warning
1156: ,p_npw_number => l_per_details_rec.npw_number
1157: );
1158: ELSE
1159: hr_utility.set_location(l_proc,280);
1160: l_per_effective_start_date := l_per_details_rec.effective_start_date;
1161: l_per_effective_end_date := l_per_details_rec.effective_end_date;
1162: END IF;
1163: --
1161: l_per_effective_end_date := l_per_details_rec.effective_end_date;
1162: END IF;
1163: --
1164: --
1165: hr_utility.set_location(l_proc,290);
1166: --
1167: -- Derive the current period of service, and create one if it does not exist
1168: --
1169: l_pds_details_rec := pds_details
1171: ,p_effective_date => l_hire_date
1172: );
1173: IF l_pds_details_rec.period_of_service_id IS NULL
1174: THEN
1175: hr_utility.set_location(l_proc,300);
1176: per_pds_ins.ins
1177: (p_person_id => p_person_id
1178: ,p_business_group_id => l_per_details_rec.business_group_id
1179: ,p_date_start => l_hire_date
1221: END IF;
1222: --
1223: -- PTU : End of changes
1224: --
1225: hr_utility.set_location(l_proc,310);
1226: --
1227: -- Derive assignment status type id for default system status type of
1228: -- ACTIVE_ASSIGN for this business group
1229: --
1233: ,p_legislation_code => l_per_details_rec.legislation_code
1234: ,p_expected_system_status => 'ACTIVE_ASSIGN'
1235: );
1236: --
1237: hr_utility.set_location(l_proc,320);
1238: --
1239: l_oversubscribed_vacancy_id :=null;
1240: --
1241: -- 2264191 changed behaviour: If p_hire_all_accepted_asgs = 'Y' then
1297: --
1298: END IF; --end of test if processing all asgs or matching single asg
1299: END LOOP;
1300: --
1301: hr_utility.set_location(l_proc,325);
1302: --
1303: -- Maintain person type usage record
1304: --
1305: /* Removed for PTU changes, since covered by previous call to maintain
1313: ,p_object_version_number => l_object_version_number
1314: );
1315: */
1316: --
1317: hr_utility.set_location(l_proc,330);
1318: --
1319: -- Call After Process User Hook
1320: --
1321: BEGIN
1342: ,p_hook_type => 'AP'
1343: );
1344: END;
1345: --
1346: hr_utility.set_location(l_proc,340);
1347: --
1348: -- When in validation only mode raise validate_enabled exception
1349: --
1350: IF p_validate
1360: p_per_effective_end_date := l_per_effective_end_date;
1361: p_assign_payroll_warning := l_assign_payroll_warning;
1362: p_oversubscribed_vacancy_id := l_oversubscribed_vacancy_id ;
1363: --
1364: hr_utility.set_location(' Leaving:'||l_proc,1000);
1365: --
1366: EXCEPTION
1367: --
1368: WHEN hr_api.validate_enabled
1440: l_oversubscribed_vacancy_id number;
1441: --
1442: begin
1443: --
1444: hr_utility.set_location('Entering:'|| l_proc, 10);
1445: --
1446: l_per_object_version_number:=p_per_object_version_number;
1447: --
1448: hr_employee_applicant_api.hire_employee_applicant
1463: p_per_effective_end_date := l_per_effective_end_date;
1464: p_unaccepted_asg_del_warning := l_unaccepted_asg_del_warning;
1465: p_assign_payroll_warning := l_assign_payroll_warning;
1466: --
1467: hr_utility.set_location('Leaving:'|| l_proc, 20);
1468: --
1469: end hire_employee_applicant;
1470: --
1471: -- ---------------------------------------------------------------------------
1768: where PAAF.assignment_id=p_asg_id;
1769: --Bug 5102289 ends here
1770: --
1771: begin
1772: hr_utility.set_location('Entering:'|| l_proc, 10);
1773: --
1774: hr_api.mandatory_arg_error
1775: (p_api_name => l_proc
1776: ,p_argument => 'person_id'
1786: -- Issue a savepoint.
1787: --
1788: savepoint hire_employee_applicant;
1789: --
1790: hr_utility.set_location(l_proc, 20);
1791: --
1792: -- Process Logic
1793: --
1794: -- Truncate the time portion from all date parameters
1821: end;
1822: --
1823: -- Check that there are not any future changes to the assignment
1824: --
1825: hr_utility.set_location(l_proc, 30);
1826: --
1827: open csr_future_asg_changes;
1828: fetch csr_future_asg_changes into l_exists;
1829: --
1828: fetch csr_future_asg_changes into l_exists;
1829: --
1830: if csr_future_asg_changes%FOUND then
1831: --
1832: hr_utility.set_location(l_proc,40);
1833: close csr_future_asg_changes;
1834: --
1835: hr_utility.set_message(801,'HR_7975_ASG_INV_FUTURE_ASA');
1836: hr_utility.raise_error;
1831: --
1832: hr_utility.set_location(l_proc,40);
1833: close csr_future_asg_changes;
1834: --
1835: hr_utility.set_message(801,'HR_7975_ASG_INV_FUTURE_ASA');
1836: hr_utility.raise_error;
1837: --
1838: end if;
1839: --
1832: hr_utility.set_location(l_proc,40);
1833: close csr_future_asg_changes;
1834: --
1835: hr_utility.set_message(801,'HR_7975_ASG_INV_FUTURE_ASA');
1836: hr_utility.raise_error;
1837: --
1838: end if;
1839: --
1840: hr_utility.set_location(l_proc,45);
1836: hr_utility.raise_error;
1837: --
1838: end if;
1839: --
1840: hr_utility.set_location(l_proc,45);
1841: --
1842: -- Get the derived details for the person DT instance
1843: --
1844: open csr_get_devived_details;
1853: l_apl_object_version_number;
1854: if csr_get_devived_details%NOTFOUND
1855: then
1856: --
1857: hr_utility.set_location(l_proc,50);
1858: --
1859: close csr_get_devived_details;
1860: --
1861: hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
1857: hr_utility.set_location(l_proc,50);
1858: --
1859: close csr_get_devived_details;
1860: --
1861: hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
1862: hr_utility.raise_error;
1863: --
1864: end if;
1865: close csr_get_devived_details;
1858: --
1859: close csr_get_devived_details;
1860: --
1861: hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
1862: hr_utility.raise_error;
1863: --
1864: end if;
1865: close csr_get_devived_details;
1866: --
1863: --
1864: end if;
1865: close csr_get_devived_details;
1866: --
1867: hr_utility.set_location(l_proc,55);
1868: --
1869: -- Validation in addition to Row Handlers
1870: --
1871: -- If the specified person type id is not null then check that it
1878: ,p_business_group_id => l_business_group_id
1879: ,p_expected_sys_type => 'EMP'
1880: );
1881: --
1882: hr_utility.set_location(l_proc,60);
1883: --
1884: -- Check that corresponding person is of 'EMP_APL'
1885: -- system person type.
1886: --
1886: --
1887: if l_system_person_type <> 'EMP_APL'
1888: then
1889: --
1890: hr_utility.set_location(l_proc,70);
1891: --
1892: hr_utility.set_message(800,'PER_52096_APL_INV_PERSON_TYPE');
1893: hr_utility.raise_error;
1894: end if;
1888: then
1889: --
1890: hr_utility.set_location(l_proc,70);
1891: --
1892: hr_utility.set_message(800,'PER_52096_APL_INV_PERSON_TYPE');
1893: hr_utility.raise_error;
1894: end if;
1895: --
1896: hr_utility.set_location(l_proc,80);
1889: --
1890: hr_utility.set_location(l_proc,70);
1891: --
1892: hr_utility.set_message(800,'PER_52096_APL_INV_PERSON_TYPE');
1893: hr_utility.raise_error;
1894: end if;
1895: --
1896: hr_utility.set_location(l_proc,80);
1897: --
1892: hr_utility.set_message(800,'PER_52096_APL_INV_PERSON_TYPE');
1893: hr_utility.raise_error;
1894: end if;
1895: --
1896: hr_utility.set_location(l_proc,80);
1897: --
1898: -- Check that corresponding person is of 'ACCEPTED' of
1899: -- assignment status type.
1900: --
1903: --close csr_chk_asg_status; -- Bug 3266844. Commented out.
1904: --
1905: if l_count = 0 then
1906: --
1907: hr_utility.set_location(l_proc,90);
1908: --
1909: close csr_chk_asg_status;
1910: --
1911: hr_utility.set_message(800,'PER_52098_APL_INV_ASG_STATUS');
1907: hr_utility.set_location(l_proc,90);
1908: --
1909: close csr_chk_asg_status;
1910: --
1911: hr_utility.set_message(800,'PER_52098_APL_INV_ASG_STATUS');
1912: hr_utility.raise_error;
1913: --
1914: end if;
1915: --
1908: --
1909: close csr_chk_asg_status;
1910: --
1911: hr_utility.set_message(800,'PER_52098_APL_INV_ASG_STATUS');
1912: hr_utility.raise_error;
1913: --
1914: end if;
1915: --
1916: close csr_chk_asg_status; -- Bug 3266844. Added.
1918: -- must be not null.
1919: --
1920: if p_overwrite_primary='Y' then
1921: --
1922: hr_utility.set_location(l_proc,100);
1923: --
1924: hr_api.mandatory_arg_error
1925: (p_api_name => l_proc
1926: ,p_argument => 'assignment_id'
1926: ,p_argument => 'assignment_id'
1927: ,p_argument_value => p_primary_assignment_id
1928: );
1929: --
1930: hr_utility.set_location(l_proc,110);
1931: else
1932: --the primary assignment id should be null
1933: l_primary_assignment_id:=null;
1934: hr_utility.set_location(l_proc,115);
1930: hr_utility.set_location(l_proc,110);
1931: else
1932: --the primary assignment id should be null
1933: l_primary_assignment_id:=null;
1934: hr_utility.set_location(l_proc,115);
1935: --
1936: end if;
1937: --
1938: hr_utility.set_location(l_proc,120);
1934: hr_utility.set_location(l_proc,115);
1935: --
1936: end if;
1937: --
1938: hr_utility.set_location(l_proc,120);
1939: --
1940: -- Check p_assignment is corresponding data.
1941: -- The assignment record specified by P_ASSIGNMENT_ID on the hire
1942: -- date in the PER_ASSIGNMENTS_F table has assignment status
1943: -- 'ACCEPTED'.
1944: --
1945: if p_primary_assignment_id is not null then
1946: --
1947: hr_utility.set_location(l_proc,130);
1948: --
1949: open csr_chk_assignment_id;
1950: fetch csr_chk_assignment_id
1951: into l_chk_person_id,
1951: into l_chk_person_id,
1952: l_chk_system_status;
1953: if csr_chk_assignment_id%NOTFOUND then
1954: --
1955: hr_utility.set_location(l_proc,140);
1956: --
1957: close csr_chk_assignment_id;
1958: --
1959: hr_utility.set_message(800,'PER_52099_ASG_INV_ASG_ID');
1955: hr_utility.set_location(l_proc,140);
1956: --
1957: close csr_chk_assignment_id;
1958: --
1959: hr_utility.set_message(800,'PER_52099_ASG_INV_ASG_ID');
1960: hr_utility.raise_error;
1961: --
1962: end if;
1963: --
1956: --
1957: close csr_chk_assignment_id;
1958: --
1959: hr_utility.set_message(800,'PER_52099_ASG_INV_ASG_ID');
1960: hr_utility.raise_error;
1961: --
1962: end if;
1963: --
1964: if l_chk_person_id <> p_person_id then
1962: end if;
1963: --
1964: if l_chk_person_id <> p_person_id then
1965: --
1966: hr_utility.set_location(l_proc,150);
1967: --
1968: close csr_chk_assignment_id;
1969: --
1970: hr_utility.set_message(800,'PER_52101_ASG_INV_PER_ID_COMB');
1966: hr_utility.set_location(l_proc,150);
1967: --
1968: close csr_chk_assignment_id;
1969: --
1970: hr_utility.set_message(800,'PER_52101_ASG_INV_PER_ID_COMB');
1971: hr_utility.raise_error;
1972: --
1973: end if;
1974: --
1967: --
1968: close csr_chk_assignment_id;
1969: --
1970: hr_utility.set_message(800,'PER_52101_ASG_INV_PER_ID_COMB');
1971: hr_utility.raise_error;
1972: --
1973: end if;
1974: --
1975: if l_chk_system_status <> 'ACCEPTED' then
1973: end if;
1974: --
1975: if l_chk_system_status <> 'ACCEPTED' then
1976: --
1977: hr_utility.set_location(l_proc,155);
1978: --
1979: close csr_chk_assignment_id;
1980: --
1981: hr_utility.set_message(800,'PER_52100_ASG_INV_PER_TYPE');
1977: hr_utility.set_location(l_proc,155);
1978: --
1979: close csr_chk_assignment_id;
1980: --
1981: hr_utility.set_message(800,'PER_52100_ASG_INV_PER_TYPE');
1982: hr_utility.raise_error;
1983: --
1984: end if;
1985: --
1978: --
1979: close csr_chk_assignment_id;
1980: --
1981: hr_utility.set_message(800,'PER_52100_ASG_INV_PER_TYPE');
1982: hr_utility.raise_error;
1983: --
1984: end if;
1985: --
1986: hr_utility.set_location(l_proc,160);
1982: hr_utility.raise_error;
1983: --
1984: end if;
1985: --
1986: hr_utility.set_location(l_proc,160);
1987: --
1988: close csr_chk_assignment_id;
1989: --
1990: end if;
1988: close csr_chk_assignment_id;
1989: --
1990: end if;
1991: --
1992: hr_utility.set_location(l_proc,170);
1993: --
1994: -- Lock the person record in PER_ALL_PEOPLE_F ready for UPDATE at a later point.
1995: -- (Note: This is necessary because calling the table handlers in locking
1996: -- ladder order invokes an error in per_apl_upd.upd due to the person
2009: ,p_validation_start_date => l_validation_start_date
2010: ,p_validation_end_date => l_validation_end_date
2011: );
2012: --
2013: hr_utility.set_location(l_proc,180);
2014: --
2015: -- Update the application details by calling the upd procedure in the
2016: -- application table handler:
2017: -- Date_end is set to l_hire_date - 1;
2022: ,p_object_version_number => l_apl_object_version_number
2023: ,p_effective_date => l_hire_date-1
2024: ,p_validate => false
2025: );
2026: hr_utility.set_location(l_proc,190);
2027: --
2028: -- Set all unaccepted applicant assignments to have end date = p_hire_date -1
2029: -- by calling the del procedure in the PER_ASSIGNMENTS_F table handler
2030: -- (This is a datetrack DELETE mode operation)
2035: into l_assignment_id,
2036: l_asg_object_version_number;
2037: exit when csr_get_un_accepted%NOTFOUND;
2038: --
2039: hr_utility.set_location(l_proc,200);
2040: --
2041: per_asg_del.del
2042: (p_assignment_id => l_assignment_id
2043: ,p_effective_start_date => l_effective_start_date
2050: ,p_datetrack_mode => 'DELETE'
2051: ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2052: );
2053: --
2054: hr_utility.set_location(l_proc,210);
2055: --
2056: l_unaccepted_asg_del_warning := TRUE;
2057: --
2058: end loop;
2058: end loop;
2059: --
2060: close csr_get_un_accepted;
2061: --
2062: hr_utility.set_location(l_proc, 220);
2063:
2064: -- PTU : Changes
2065:
2066: l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
2067: (l_business_group_id,
2068: 'EMP');
2069: -- PTU : End of Changes
2070:
2071: hr_utility.set_location(l_proc, 225);
2072:
2073: --
2074: -- Update the person details by calling upd procedure in
2075: -- the per_all_people_f table.
2096: ,p_orig_hire_warning => l_orig_hire_warning
2097: ,p_npw_number => l_npw_number
2098: );
2099: --
2100: hr_utility.set_location(l_proc,230);
2101:
2102: -- PTU : Following Code has been added
2103: --
2104: hr_per_type_usage_internal.maintain_person_type_usage
2138: ,p_legislation_code => l_legislation_code
2139: ,p_expected_system_status => 'ACTIVE_ASSIGN'
2140: );
2141: --
2142: hr_utility.set_location(l_proc,240);
2143: --
2144: l_oversubscribed_vacancy_id :=null;
2145: --
2146: -- #2468916: Need to retrieve the period of service id
2149: close get_primary;
2150: --
2151: for asg_rec in csr_get_accepted loop
2152: --
2153: hr_utility.set_location(l_proc,250);
2154: --
2155: if asg_rec.effective_start_date=l_hire_date then
2156: l_datetrack_update_mode:='CORRECTION';
2157: else
2182: p_org_now_no_manager_warning => l_org_now_no_manager_warning,
2183: p_hourly_salaried_warning => l_hourly_salaried_warning
2184: );
2185: --
2186: hr_utility.set_location(l_proc,260);
2187: --The below has been commented as part of bug fix 5481530
2188: -- if asg_rec.assignment_id = p_primary_assignment_id then
2189: if asg_rec.assignment_id = l_primary_assignment_id then
2190: hr_assignment_api.set_new_primary_asg
2222: -- Bug 4644830 End
2223: -- Bug 4630129 Starts
2224: elsif asg_rec.assignment_id = p_primary_assignment_id and p_overwrite_primary ='Y' then
2225: -- Hire the new secondary Applicant assignment.
2226: hr_utility.set_location(l_proc,261);
2227: per_asg_upd.upd
2228: (p_assignment_id => asg_rec.assignment_id,
2229: p_object_version_number => asg_rec.object_version_number,
2230: p_effective_date => l_hire_date,
2246: p_org_now_no_manager_warning => l_org_now_no_manager_warning,
2247: p_hourly_salaried_warning => l_hourly_salaried_warning
2248: );
2249: -- Make the new secondary Applicant assignment Primary.
2250: hr_utility.set_location(l_proc,262);
2251: hr_assignment_api.set_new_primary_asg
2252: (p_validate => false
2253: ,p_effective_date => l_hire_date
2254: ,p_person_id => p_person_id
2256: ,p_object_version_number => asg_rec.object_version_number
2257: ,p_effective_start_date => l_effective_start_date
2258: ,p_effective_end_date => l_effective_end_date
2259: );
2260: hr_utility.set_location(l_proc,263);
2261: -- Bug 4630129 Ends
2262: -- Bug 4644830 Start
2263: OPEN get_pay_proposal(asg_rec.assignment_id);
2264: FETCH get_pay_proposal INTO l_pay_pspl_id,l_pay_obj_number,l_proposed_sal_n, l_dummy_change_date,l_proposal_reason; --Added Proposal_Reason for Bug # 5987409 --
2294: open get_primary;
2295: fetch get_primary into l_primary_asg_id,l_primary_ovn, l_period_of_service_id; -- #2468916
2296: close get_primary;
2297: --
2298: hr_utility.set_location(l_proc, 264);
2299: --
2300: open get_asg(asg_rec.assignment_id);
2301: fetch get_asg into l_asg_rec;
2302: close get_asg;
2308: if get_primary_approved_proposal%found then
2309: close get_primary_approved_proposal;
2310:
2311: if l_asg_rec.pay_basis_id is null then
2312: hr_utility.set_message(800,'HR_289767_SALARY_BASIS_IS_NULL');
2313: hr_utility.raise_error;
2314: end if;
2315: --Added else to close the cursor--5277866
2316: else
2309: close get_primary_approved_proposal;
2310:
2311: if l_asg_rec.pay_basis_id is null then
2312: hr_utility.set_message(800,'HR_289767_SALARY_BASIS_IS_NULL');
2313: hr_utility.raise_error;
2314: end if;
2315: --Added else to close the cursor--5277866
2316: else
2317: close get_primary_approved_proposal;
2319: ---changes for 4959033 ends here
2320:
2321: if l_asg_rec.people_group_id is not null then
2322: --
2323: hr_utility.set_location(l_proc, 265);
2324: --
2325: open get_pgp(l_asg_rec.people_group_id);
2326: fetch get_pgp into l_pgp_rec;
2327: close get_pgp;
2329: end if;
2330:
2331: if l_asg_rec.soft_coding_keyflex_id is not null then
2332: --
2333: hr_utility.set_location(l_proc, 266);
2334: --
2335: open get_scl(l_asg_rec.soft_coding_keyflex_id);
2336: fetch get_scl into l_scl_rec;
2337: close get_scl;
2339: end if;
2340: --
2341: if l_asg_rec.cagr_grade_def_id is not null then
2342: --
2343: hr_utility.set_location(l_proc, 267);
2344: --
2345: open get_cag(l_asg_rec.cagr_grade_def_id);
2346: fetch get_cag into l_cag_rec;
2347: close get_cag;
2347: close get_cag;
2348: end if;
2349: --
2350:
2351: hr_utility.set_location(l_proc, 268);
2352: --
2353:
2354: --The below call has been commented as per bug 5102160
2355: -- soft_coding_keyflex_id is passed by calling the new update_emp_asg_criteria procedure
2469: ,p_gsp_post_process_warning => l_gsp_post_process_warning -- bug 2999562
2470: );
2471:
2472: --
2473: hr_utility.set_location(l_proc, 269);
2474: --
2475: hr_assignment_api.update_emp_asg
2476: (p_validate => FALSE
2477: ,p_effective_date => l_hire_date
2601: ,p_other_manager_warning => l_dummyb
2602: ,p_hourly_salaried_warning => l_dummyb
2603: );
2604: --
2605: hr_utility.set_location(l_proc, 271);
2606:
2607: --Fix For Bug # 5987409 Starts -----
2608:
2609: UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_asg_rec.vacancy_id ,
2630: ,p_datetrack_mode => 'DELETE'
2631: ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2632: );
2633: --
2634: hr_utility.set_location(l_proc, 272);
2635: --
2636: --
2637:
2638: l_pspl_asg_id :=asg_rec.assignment_id;
2692: -- fix for the bug 5024006 ends here
2693:
2694: else
2695: --
2696: hr_utility.set_location(l_proc, 270);
2697: --
2698: -- we must update the old assignment with the new assignment record
2699: --
2700: open get_primary;
2700: open get_primary;
2701: fetch get_primary into l_primary_asg_id,l_primary_ovn, l_period_of_service_id; -- #2468916
2702: close get_primary;
2703: --
2704: hr_utility.set_location(l_proc, 280);
2705: --
2706: open get_asg(asg_rec.assignment_id);
2707: fetch get_asg into l_asg_rec;
2708: close get_asg;
2706: open get_asg(asg_rec.assignment_id);
2707: fetch get_asg into l_asg_rec;
2708: close get_asg;
2709: --
2710: hr_utility.set_location(l_proc, 290);
2711: --
2712: if l_asg_rec.people_group_id is not null then
2713: --
2714: hr_utility.set_location(l_proc, 300);
2710: hr_utility.set_location(l_proc, 290);
2711: --
2712: if l_asg_rec.people_group_id is not null then
2713: --
2714: hr_utility.set_location(l_proc, 300);
2715: --
2716: open get_pgp(l_asg_rec.people_group_id);
2717: fetch get_pgp into l_pgp_rec;
2718: close get_pgp;
2719: end if;
2720: --
2721: if l_asg_rec.soft_coding_keyflex_id is not null then
2722: --
2723: hr_utility.set_location(l_proc, 310);
2724: --
2725: open get_scl(l_asg_rec.soft_coding_keyflex_id);
2726: fetch get_scl into l_scl_rec;
2727: close get_scl;
2728: end if;
2729: --
2730: if l_asg_rec.cagr_grade_def_id is not null then
2731: --
2732: hr_utility.set_location(l_proc, 320);
2733: --
2734: open get_cag(l_asg_rec.cagr_grade_def_id);
2735: fetch get_cag into l_cag_rec;
2736: close get_cag;
2735: fetch get_cag into l_cag_rec;
2736: close get_cag;
2737: end if;
2738: --
2739: hr_utility.set_location(l_proc, 330);
2740: --
2741: if p_overwrite_primary = 'V' then
2742: --
2743: open get_asg(l_primary_asg_id);
3097: ,p_concatenated_segments => l_concatenated_segments
3098: ,p_gsp_post_process_warning => l_gsp_post_process_warning -- bug 2999562
3099: );
3100: --
3101: hr_utility.set_location(l_proc, 340);
3102: --
3103: hr_assignment_api.update_emp_asg
3104: (p_validate => FALSE
3105: ,p_effective_date => l_hire_date
3229: ,p_other_manager_warning => l_dummyb
3230: ,p_hourly_salaried_warning => l_dummyb
3231: );
3232: --
3233: hr_utility.set_location(l_proc, 350);
3234:
3235:
3236: --Fix For Bug # 5987409 Starts -----
3237:
3259: ,p_datetrack_mode => 'DELETE'
3260: ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
3261: );
3262: --
3263: hr_utility.set_location(l_proc, 360);
3264: --
3265: -- added for the bug 4641965
3266: --
3267: if (p_primary_assignment_id is not null ) then
3364: end if;
3365: --
3366: end loop;
3367: --
3368: hr_utility.set_location(l_proc,370);
3369: --
3370: -- Maintain person type usage record
3371: --
3372: -- PTU : Commented call to maintain_ptu
3420: p_unaccepted_asg_del_warning := l_unaccepted_asg_del_warning;
3421: p_assign_payroll_warning := l_assign_payroll_warning;
3422: p_oversubscribed_vacancy_id := l_oversubscribed_vacancy_id ;
3423: --
3424: hr_utility.set_location(' Leaving:'||l_proc, 380);
3425: --
3426: exception
3427: when hr_api.validate_enabled then
3428: --
3442: p_unaccepted_asg_del_warning := l_unaccepted_asg_del_warning;
3443: p_assign_payroll_warning := l_assign_payroll_warning;
3444: p_oversubscribed_vacancy_id := l_oversubscribed_vacancy_id ;
3445: --
3446: hr_utility.set_location(' Leaving:'||l_proc, 390);
3447: --
3448: when others then
3449: --
3450: -- A validation or unexpected error has occurred
3460: --
3461: -- set in out parameters and set out parameters
3462: --
3463:
3464: hr_utility.set_location(' Leaving:'||l_proc, 400);
3465: raise;
3466: --
3467: end hire_employee_applicant;
3468: --
3477: --
3478: l_index number;
3479: l_max_ele number;
3480: begin
3481: hr_utility.set_location('IN locate_element',50);
3482: l_index := 0;
3483: l_max_ele := p_table.COUNT;
3484: hr_utility.trace(' table rows: '||to_char(l_max_ele));
3485: if l_max_ele > 0 then
3480: begin
3481: hr_utility.set_location('IN locate_element',50);
3482: l_index := 0;
3483: l_max_ele := p_table.COUNT;
3484: hr_utility.trace(' table rows: '||to_char(l_max_ele));
3485: if l_max_ele > 0 then
3486: l_index := 1;
3487: loop
3488: if p_table(l_index).id = p_id then
3491: l_index := l_index + 1;
3492: EXIT when l_index > l_max_ele ;
3493: end loop;
3494: end if;
3495: hr_utility.trace(' index found : '||to_char(l_index));
3496: hr_utility.set_location('OUT locate_element',51);
3497:
3498: if l_index > l_max_ele then
3499: return(0);
3492: EXIT when l_index > l_max_ele ;
3493: end loop;
3494: end if;
3495: hr_utility.trace(' index found : '||to_char(l_index));
3496: hr_utility.set_location('OUT locate_element',51);
3497:
3498: if l_index > l_max_ele then
3499: return(0);
3500: else