[Home] [Help]
82: l_proc varchar2(20) :='get_prev_person_type';
83:
84: begin
85:
86: hr_utility.set_location('Entering :'||g_package||l_proc,10);
87: hr_utility.set_location('P_business_group_id :'||P_business_group_id,11);
88: hr_utility.set_location('p_person_id :'||p_person_id,12);
89: hr_utility.set_location('p_current_person_type :'||p_current_person_type,13);
90: hr_utility.set_location('p_effective_date :'||p_effective_date,14);
83:
84: begin
85:
86: hr_utility.set_location('Entering :'||g_package||l_proc,10);
87: hr_utility.set_location('P_business_group_id :'||P_business_group_id,11);
88: hr_utility.set_location('p_person_id :'||p_person_id,12);
89: hr_utility.set_location('p_current_person_type :'||p_current_person_type,13);
90: hr_utility.set_location('p_effective_date :'||p_effective_date,14);
91:
84: begin
85:
86: hr_utility.set_location('Entering :'||g_package||l_proc,10);
87: hr_utility.set_location('P_business_group_id :'||P_business_group_id,11);
88: hr_utility.set_location('p_person_id :'||p_person_id,12);
89: hr_utility.set_location('p_current_person_type :'||p_current_person_type,13);
90: hr_utility.set_location('p_effective_date :'||p_effective_date,14);
91:
92: open csr_person_types;
85:
86: hr_utility.set_location('Entering :'||g_package||l_proc,10);
87: hr_utility.set_location('P_business_group_id :'||P_business_group_id,11);
88: hr_utility.set_location('p_person_id :'||p_person_id,12);
89: hr_utility.set_location('p_current_person_type :'||p_current_person_type,13);
90: hr_utility.set_location('p_effective_date :'||p_effective_date,14);
91:
92: open csr_person_types;
93: fetch csr_person_types bulk collect into l_person_types;
86: hr_utility.set_location('Entering :'||g_package||l_proc,10);
87: hr_utility.set_location('P_business_group_id :'||P_business_group_id,11);
88: hr_utility.set_location('p_person_id :'||p_person_id,12);
89: hr_utility.set_location('p_current_person_type :'||p_current_person_type,13);
90: hr_utility.set_location('p_effective_date :'||p_effective_date,14);
91:
92: open csr_person_types;
93: fetch csr_person_types bulk collect into l_person_types;
94: if l_person_types.count = 0 then --csr_person_types%notfound then
97: close csr_prev_person_types;
98: end if;
99: close csr_person_types;
100:
101: hr_utility.set_location('p_system_person_type :'||p_system_person_type,20);
102: if p_system_person_type is null then
103: FOR v_counter IN l_person_types.FIRST .. l_person_types.LAST
104: LOOP
105: if l_person_types(v_counter) = 'EMP' then
108: end if;
109: END LOOP;
110: end if;
111:
112: hr_utility.set_location('p_system_person_type :'||p_system_person_type,30);
113: if p_system_person_type is null then
114: FOR v_counter IN l_person_types.FIRST .. l_person_types.LAST
115: LOOP
116: if l_person_types(v_counter) = 'APL' then
126: end if;
127: END LOOP;
128: end if;
129:
130: hr_utility.set_location('p_system_person_type :'||p_system_person_type,40);
131: if p_system_person_type is null then
132: FOR v_counter IN l_person_types.FIRST .. l_person_types.LAST
133: LOOP
134: if l_person_types(v_counter) = 'EX_EMP' then
144: end if;
145: END LOOP;
146: end if;
147:
148: hr_utility.set_location('p_system_person_type :'||p_system_person_type,50);
149: if p_system_person_type is null then
150: FOR v_counter IN l_person_types.FIRST .. l_person_types.LAST
151: LOOP
152: if l_person_types(v_counter) = 'EX_APL' then
155: end if;
156: END LOOP;
157: end if;
158:
159: hr_utility.set_location('p_system_person_type :'||p_system_person_type,60);
160: if p_system_person_type is null then
161: open csr_prev_ppl_person_types;
162: fetch csr_prev_ppl_person_types into p_system_person_type;
163: if csr_prev_ppl_person_types%notfound then
165: end if;
166: close csr_prev_ppl_person_types;
167: end if;
168:
169: hr_utility.set_location('p_system_person_type :'||p_system_person_type,99);
170: hr_utility.set_location('Leaving :'||l_proc,100);
171:
172: end get_prev_person_type;
173: --
166: close csr_prev_ppl_person_types;
167: end if;
168:
169: hr_utility.set_location('p_system_person_type :'||p_system_person_type,99);
170: hr_utility.set_location('Leaving :'||l_proc,100);
171:
172: end get_prev_person_type;
173: --
174: --
1041: --
1042: CLOSE csr_get_ptu_id;
1043: --
1044: -- # 3690364 - changed application_id from 801 to 800
1045: hr_utility.set_message(800,'HR_289548_PEM_EMP_PERSON_ID');
1046: hr_utility.raise_error;
1047: --
1048: END IF;
1049: CLOSE csr_get_ptu_id;
1042: CLOSE csr_get_ptu_id;
1043: --
1044: -- # 3690364 - changed application_id from 801 to 800
1045: hr_utility.set_message(800,'HR_289548_PEM_EMP_PERSON_ID');
1046: hr_utility.raise_error;
1047: --
1048: END IF;
1049: CLOSE csr_get_ptu_id;
1050: -- end Bug 3285486
1051: if p_cancel_type = 'HIRE' then
1052: if p_where = 'BEGIN' then
1053: --
1054:
1055: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',1);
1056: --
1057: -- Start Bug 3285486. commented the call to hr_person.chk_future_person_type
1058: -- added the new call to hr_person_type_usage_info.FutSysPerTypeChgExists
1059: /* if hr_person.chk_future_person_type(p_system_person_type
1065: ,p_effective_date => p_primary_date
1066: ,p_person_id => p_person_id ) THEN
1067: -- End Bug 3285486.
1068: --
1069: hr_utility.set_message(801,'HR_7078_EMP_ENTER_CANCEL_TYPE');
1070: hr_utility.raise_error;
1071: --
1072: end if;
1073:
1066: ,p_person_id => p_person_id ) THEN
1067: -- End Bug 3285486.
1068: --
1069: hr_utility.set_message(801,'HR_7078_EMP_ENTER_CANCEL_TYPE');
1070: hr_utility.raise_error;
1071: --
1072: end if;
1073:
1074: -- start changes for bug 8405711
1078: fetch csr_application_change_exists into l_dummy;
1079:
1080: if csr_application_change_exists%FOUND then
1081: close csr_application_change_exists;
1082: hr_utility.set_message(800,'PER_449844_APL_ACTIONS_EXISTS');
1083: hr_utility.set_message_token('PERSON_STATUS','hire');
1084: hr_utility.set_message_token('PERSON_TYPE','Employee');
1085: hr_utility.raise_error;
1086: end if;
1079:
1080: if csr_application_change_exists%FOUND then
1081: close csr_application_change_exists;
1082: hr_utility.set_message(800,'PER_449844_APL_ACTIONS_EXISTS');
1083: hr_utility.set_message_token('PERSON_STATUS','hire');
1084: hr_utility.set_message_token('PERSON_TYPE','Employee');
1085: hr_utility.raise_error;
1086: end if;
1087:
1080: if csr_application_change_exists%FOUND then
1081: close csr_application_change_exists;
1082: hr_utility.set_message(800,'PER_449844_APL_ACTIONS_EXISTS');
1083: hr_utility.set_message_token('PERSON_STATUS','hire');
1084: hr_utility.set_message_token('PERSON_TYPE','Employee');
1085: hr_utility.raise_error;
1086: end if;
1087:
1088: close csr_application_change_exists;
1081: close csr_application_change_exists;
1082: hr_utility.set_message(800,'PER_449844_APL_ACTIONS_EXISTS');
1083: hr_utility.set_message_token('PERSON_STATUS','hire');
1084: hr_utility.set_message_token('PERSON_TYPE','Employee');
1085: hr_utility.raise_error;
1086: end if;
1087:
1088: close csr_application_change_exists;
1089: --
1095: fetch csr_assign_actions_exist into l_dummy;
1096: --
1097: if csr_assign_actions_exist%FOUND then
1098: close csr_assign_actions_exist;
1099: hr_utility.set_message(800,'PER_289566_ASG_ACTIONS_EXISTS');
1100: hr_utility.raise_error;
1101: end if;
1102: --
1103: close csr_assign_actions_exist;
1096: --
1097: if csr_assign_actions_exist%FOUND then
1098: close csr_assign_actions_exist;
1099: hr_utility.set_message(800,'PER_289566_ASG_ACTIONS_EXISTS');
1100: hr_utility.raise_error;
1101: end if;
1102: --
1103: close csr_assign_actions_exist;
1104: -- BUG 2964027 ENDS HERE.
1102: --
1103: close csr_assign_actions_exist;
1104: -- BUG 2964027 ENDS HERE.
1105: --
1106: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',2);
1107: if not hr_person.chk_prev_person_type(p_system_person_type
1108: ,p_person_id
1109: ,p_business_group_id
1110: ,p_primary_date) then
1108: ,p_person_id
1109: ,p_business_group_id
1110: ,p_primary_date) then
1111: --
1112: hr_utility.set_message(801,'HR_7077_NO_CANCEL_HIRE');
1113: hr_utility.raise_error;
1114: --
1115: end if;
1116:
1109: ,p_business_group_id
1110: ,p_primary_date) then
1111: --
1112: hr_utility.set_message(801,'HR_7077_NO_CANCEL_HIRE');
1113: hr_utility.raise_error;
1114: --
1115: end if;
1116:
1117: -- check for pay actions.Start Bug 2841901
1115: end if;
1116:
1117: -- check for pay actions.Start Bug 2841901
1118:
1119: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',3);
1120: open csr_payactions;
1121: fetch csr_payactions into l_dummy;
1122: --
1123: if csr_payactions%found then
1126: -- Bug# 2989638 Start Here
1127: -- Description : Added new message to display proper error message
1128: --
1129: --
1130: hr_utility.set_message(800,'HR_289529_EMP_FUT_PAY_EXIST');
1131: --
1132: -- Bug# 2989638 End Here
1133: --
1134: hr_utility.raise_error;
1130: hr_utility.set_message(800,'HR_289529_EMP_FUT_PAY_EXIST');
1131: --
1132: -- Bug# 2989638 End Here
1133: --
1134: hr_utility.raise_error;
1135: end if;
1136: --
1137: close csr_payactions;
1138: -- End Bug 2841901
1138: -- End Bug 2841901
1139: --
1140: -- check for US pay actions.Start Bug 6798826
1141:
1142: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',31);
1143: if
1144: (per_cancel_hire_or_apl_pkg.return_legislation_code(p_person_id)='US') then
1145:
1146:
1143: if
1144: (per_cancel_hire_or_apl_pkg.return_legislation_code(p_person_id)='US') then
1145:
1146:
1147: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',32);
1148: open csr_payactions_us;
1149: fetch csr_payactions_us into l_dummy;
1150: --
1151: if csr_payactions_us%found then
1150: --
1151: if csr_payactions_us%found then
1152: close csr_payactions_us;
1153: --
1154: hr_utility.set_message(800,'HR_289529_EMP_FUT_PAY_EXIST');
1155:
1156: hr_utility.raise_error;
1157: end if;
1158: --
1152: close csr_payactions_us;
1153: --
1154: hr_utility.set_message(800,'HR_289529_EMP_FUT_PAY_EXIST');
1155:
1156: hr_utility.raise_error;
1157: end if;
1158: --
1159: close csr_payactions_us;
1160: end if;
1163:
1164: p_where := 'SUPERVISOR';
1165: --
1166: end if;
1167: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',4);
1168: if p_where = 'SUPERVISOR' then
1169: --
1170: open supervisor;
1171: --
1171: --
1172: fetch supervisor into l_dummy;
1173: --
1174: if supervisor%FOUND then
1175: hr_utility.set_message(801,'HR_EMP_IS_SUPER');
1176: close supervisor;
1177: return;
1178: else
1179: close supervisor;
1179: close supervisor;
1180: end if;
1181: p_where:= 'RECRUITER';
1182: end if;
1183: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',5);
1184: if p_where = 'RECRUITER' then
1185: --
1186: open recruiter;
1187: --
1187: --
1188: fetch recruiter into l_dummy;
1189: --
1190: if recruiter%FOUND then
1191: hr_utility.set_message(801,'HR_EMP_IS_RECRUITER');
1192: close recruiter;
1193: return;
1194: else
1195: close recruiter;
1195: close recruiter;
1196: end if;
1197: p_where:= 'EVENT';
1198: end if;
1199: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',6);
1200: if p_where = 'EVENT' then
1201: --
1202: open reviews_or_events(p_type =>'E');
1203: --
1203: --
1204: fetch reviews_or_events into l_dummy;
1205: --
1206: if reviews_or_events%FOUND then
1207: hr_utility.set_message(801,'HR_EMP_HAS_EVENTS');
1208: close reviews_or_events;
1209: return;
1210: else
1211: close reviews_or_events;
1212: end if;
1213: --
1214: p_where := 'INTERVIEW';
1215: end if;
1216: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',7);
1217: if p_where = 'INTERVIEW' then
1218: --
1219: open interviews;
1220: --
1220: --
1221: fetch interviews into l_dummy;
1222: --
1223: if interviews%FOUND then
1224: hr_utility.set_message(801,'HR_EMP_IS_INTERVIEWER');
1225: close interviews;
1226: return;
1227: else
1228: close interviews;
1228: close interviews;
1229: end if;
1230: p_where := 'REVIEW';
1231: end if;
1232: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',8);
1233: if p_where = 'REVIEW' then
1234: --
1235: open reviews_or_events(p_type =>'I');
1236: --
1236: --
1237: fetch reviews_or_events into l_dummy;
1238: --
1239: if reviews_or_events%FOUND then
1240: hr_utility.set_message(801,'HR_EMP_DUE_REVIEW');
1241: close reviews_or_events;
1242: return;
1243: else
1244: close reviews_or_events;
1244: close reviews_or_events;
1245: end if;
1246: p_where := 'VACANCY';
1247: end if;
1248: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',9);
1249: if p_where = 'VACANCY' then
1250: --
1251: open vacancy;
1252: --
1252: --
1253: fetch vacancy into l_dummy;
1254: --
1255: if vacancy%FOUND then
1256: hr_utility.set_message(801,'HR_EMP_VAC_RECRUITER');
1257: close vacancy;
1258: return;
1259: else
1260: close vacancy;
1260: close vacancy;
1261: end if;
1262: p_where:= 'REQUISITION';
1263: end if;
1264: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',10);
1265: if p_where = 'REQUISITION' then
1266: --
1267: open requisition;
1268: --
1268: --
1269: fetch requisition into l_dummy;
1270: --
1271: if requisition%FOUND then
1272: hr_utility.set_message(801,'HR_EMP_REQUISITIONS');
1273: close requisition;
1274: return;
1275: else
1276: close requisition;
1276: close requisition;
1277: end if;
1278: p_where:= 'BUDGET_VALUE';
1279: end if;
1280: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',11);
1281: if p_where = 'BUDGET_VALUE' then
1282: --
1283: open budget_values;
1284: --
1284: --
1285: fetch budget_values into l_dummy;
1286: --
1287: if budget_values%FOUND then
1288: hr_utility.set_message(801,'HR_EMP_BUDGET_VALUES');
1289: close budget_values;
1290: return;
1291: else
1292: close budget_values;
1292: close budget_values;
1293: end if;
1294: p_where:= 'PAYMENT';
1295: end if;
1296: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',12);
1297: if p_where = 'PAYMENT' then
1298: --
1299: open payment;
1300: --
1300: --
1301: fetch payment into l_dummy;
1302: --
1303: if payment%FOUND then
1304: hr_utility.set_message(801,'HR_EMP_PAYMENT_METHODS');
1305: close payment;
1306: return;
1307: else
1308: close payment;
1318: elsif p_cancel_type = 'APL' then
1319: --
1320: if p_where = 'BEGIN' then
1321: --
1322: hr_utility.set_location('APP1.B_PRE_DEL_CHECK',1);
1323: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',13);
1324: --
1325: -- Start Bug 3285486. commented the call to hr_person.chk_future_person_type
1326: -- added the new call to hr_person_type_usage_info.FutSysPerTypeChgExists
1319: --
1320: if p_where = 'BEGIN' then
1321: --
1322: hr_utility.set_location('APP1.B_PRE_DEL_CHECK',1);
1323: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',13);
1324: --
1325: -- Start Bug 3285486. commented the call to hr_person.chk_future_person_type
1326: -- added the new call to hr_person_type_usage_info.FutSysPerTypeChgExists
1327: --
1335: ,p_person_id => p_person_id ) THEN
1336: --
1337: -- End Bug 3285486
1338: --
1339: hr_utility.set_message(800,'HR_7080_ALL_APP_NO_CANCEL');
1340: hr_utility.raise_error;
1341: --
1342: end if;
1343: --
1336: --
1337: -- End Bug 3285486
1338: --
1339: hr_utility.set_message(800,'HR_7080_ALL_APP_NO_CANCEL');
1340: hr_utility.raise_error;
1341: --
1342: end if;
1343: --
1344: hr_utility.set_location('APP1.B_PRE_DEL_CHECK',2);
1340: hr_utility.raise_error;
1341: --
1342: end if;
1343: --
1344: hr_utility.set_location('APP1.B_PRE_DEL_CHECK',2);
1345: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',14);
1346: --
1347: if not hr_person.chk_prev_person_type(p_system_person_type
1348: ,p_person_id
1341: --
1342: end if;
1343: --
1344: hr_utility.set_location('APP1.B_PRE_DEL_CHECK',2);
1345: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',14);
1346: --
1347: if not hr_person.chk_prev_person_type(p_system_person_type
1348: ,p_person_id
1349: ,p_business_group_id
1348: ,p_person_id
1349: ,p_business_group_id
1350: ,p_primary_date) then
1351: --
1352: hr_utility.set_message(800,'HR_7081_ALL_APP_NO_CANCEL');
1353: hr_utility.raise_error;
1354: --
1355: end if;
1356: --
1349: ,p_business_group_id
1350: ,p_primary_date) then
1351: --
1352: hr_utility.set_message(800,'HR_7081_ALL_APP_NO_CANCEL');
1353: hr_utility.raise_error;
1354: --
1355: end if;
1356: --
1357: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',15);
1353: hr_utility.raise_error;
1354: --
1355: end if;
1356: --
1357: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',15);
1358:
1359: else
1360: app_exception.invalid_argument('cancel_apl',
1361: 'P_WHERE',p_where);
1363: else
1364: app_exception.invalid_argument('cancel_hire_or_apl',
1365: 'P_CANCEL_TYPE',p_cancel_type);
1366: end if;
1367: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',16);
1368: exception
1369: when hr_utility.hr_error then
1370: raise;
1371: when others then
1365: 'P_CANCEL_TYPE',p_cancel_type);
1366: end if;
1367: hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',16);
1368: exception
1369: when hr_utility.hr_error then
1370: raise;
1371: when others then
1372: hr_utility.oracle_error(sqlcode);
1373: hr_utility.raise_error;
1368: exception
1369: when hr_utility.hr_error then
1370: raise;
1371: when others then
1372: hr_utility.oracle_error(sqlcode);
1373: hr_utility.raise_error;
1374: end pre_cancel_checks;
1375: --
1376: --
1369: when hr_utility.hr_error then
1370: raise;
1371: when others then
1372: hr_utility.oracle_error(sqlcode);
1373: hr_utility.raise_error;
1374: end pre_cancel_checks;
1375: --
1376: --
1377: -- fix 7410493
1638: --
1639: return v_dummy;
1640: exception
1641: when no_data_found then
1642: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
1643: hr_utility.raise_error;
1644: end;
1645:
1646:
1639: return v_dummy;
1640: exception
1641: when no_data_found then
1642: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
1643: hr_utility.raise_error;
1644: end;
1645:
1646:
1647: begin
1645:
1646:
1647: begin
1648:
1649: hr_utility.set_location('cancel_emp_apl_hire ',10);
1650:
1651: -- FIRST PERFROM ALL THE CHECKS TO SEE IF THE CANCEL HIRE PROCESS CAN BE
1652: -- MADE WITH OUT ANY ISSUE.
1653: /*
1658: --
1659: CLOSE csr_get_ptu_id;
1660: --
1661: -- # 3690364 - changed application_id from 801 to 800
1662: hr_utility.set_message(800,'HR_289548_PEM_EMP_PERSON_ID');
1663: hr_utility.raise_error;
1664: --
1665: END IF;
1666: CLOSE csr_get_ptu_id;
1659: CLOSE csr_get_ptu_id;
1660: --
1661: -- # 3690364 - changed application_id from 801 to 800
1662: hr_utility.set_message(800,'HR_289548_PEM_EMP_PERSON_ID');
1663: hr_utility.raise_error;
1664: --
1665: END IF;
1666: CLOSE csr_get_ptu_id;
1667:
1664: --
1665: END IF;
1666: CLOSE csr_get_ptu_id;
1667:
1668: hr_utility.set_location('cancel_emp_apl_hire ',11);
1669:
1670: IF hr_person_type_usage_info.FutSysPerTypeChgExists
1671: (p_person_type_usage_id => l_person_type_usage_id
1672: ,p_effective_date => p_date_start
1672: ,p_effective_date => p_date_start
1673: ,p_person_id => p_person_id ) THEN
1674: -- End Bug 3285486.
1675: --
1676: hr_utility.set_message(801,'HR_7078_EMP_ENTER_CANCEL_TYPE');
1677: hr_utility.raise_error;
1678: --
1679: end if;
1680: */
1673: ,p_person_id => p_person_id ) THEN
1674: -- End Bug 3285486.
1675: --
1676: hr_utility.set_message(801,'HR_7078_EMP_ENTER_CANCEL_TYPE');
1677: hr_utility.raise_error;
1678: --
1679: end if;
1680: */
1681: if p_period_of_service_id is null then
1680: */
1681: if p_period_of_service_id is null then
1682: l_period_of_service_id:= get_period_of_service(p_person_id => p_person_id
1683: ,p_start_date =>p_date_start);
1684: hr_utility.set_location('cancel_emp_apl_hire ',20);
1685: else
1686: l_period_of_service_id := p_period_of_service_id;
1687: hr_utility.set_location('cancel_emp_apl_hire',30);
1688: end if;
1683: ,p_start_date =>p_date_start);
1684: hr_utility.set_location('cancel_emp_apl_hire ',20);
1685: else
1686: l_period_of_service_id := p_period_of_service_id;
1687: hr_utility.set_location('cancel_emp_apl_hire',30);
1688: end if;
1689: hr_utility.set_location('cancel_emp_apl_hire ',40);
1690:
1691: hr_utility.set_location('cancel_emp_apl_hire ',12);
1685: else
1686: l_period_of_service_id := p_period_of_service_id;
1687: hr_utility.set_location('cancel_emp_apl_hire',30);
1688: end if;
1689: hr_utility.set_location('cancel_emp_apl_hire ',40);
1690:
1691: hr_utility.set_location('cancel_emp_apl_hire ',12);
1692:
1693: open csr_assign_actions_exist;
1687: hr_utility.set_location('cancel_emp_apl_hire',30);
1688: end if;
1689: hr_utility.set_location('cancel_emp_apl_hire ',40);
1690:
1691: hr_utility.set_location('cancel_emp_apl_hire ',12);
1692:
1693: open csr_assign_actions_exist;
1694: fetch csr_assign_actions_exist into l_dummy;
1695:
1694: fetch csr_assign_actions_exist into l_dummy;
1695:
1696: if csr_assign_actions_exist%FOUND then
1697: close csr_assign_actions_exist;
1698: hr_utility.set_message(800,'PER_289566_ASG_ACTIONS_EXISTS');
1699: hr_utility.raise_error;
1700: end if;
1701: close csr_assign_actions_exist;
1702:
1695:
1696: if csr_assign_actions_exist%FOUND then
1697: close csr_assign_actions_exist;
1698: hr_utility.set_message(800,'PER_289566_ASG_ACTIONS_EXISTS');
1699: hr_utility.raise_error;
1700: end if;
1701: close csr_assign_actions_exist;
1702:
1703: hr_utility.set_location('cancel_emp_apl_hire ',14);
1699: hr_utility.raise_error;
1700: end if;
1701: close csr_assign_actions_exist;
1702:
1703: hr_utility.set_location('cancel_emp_apl_hire ',14);
1704:
1705: open csr_payactions;
1706: fetch csr_payactions into l_dummy;
1707: --
1706: fetch csr_payactions into l_dummy;
1707: --
1708: if csr_payactions%found then
1709: close csr_payactions;
1710: hr_utility.set_message(800,'HR_289529_EMP_FUT_PAY_EXIST');
1711: hr_utility.raise_error;
1712: end if;
1713: --
1714: close csr_payactions;
1707: --
1708: if csr_payactions%found then
1709: close csr_payactions;
1710: hr_utility.set_message(800,'HR_289529_EMP_FUT_PAY_EXIST');
1711: hr_utility.raise_error;
1712: end if;
1713: --
1714: close csr_payactions;
1715: hr_utility.set_location('cancel_emp_apl_hire ',15);
1711: hr_utility.raise_error;
1712: end if;
1713: --
1714: close csr_payactions;
1715: hr_utility.set_location('cancel_emp_apl_hire ',15);
1716: --
1717: l_business_group_id := p_business_group_id;-- fix for bug 5005157 .
1718:
1719:
1720: open assignments;
1721: <
1722: loop
1723: -- VT #438579 03/05/79 added assignment_id
1724: hr_utility.set_location('cancel_emp_apl_hire ',50);
1725: fetch assignments into p_rowid,l_assignment_id,l_asg_status_type_id; -- Bug 3564129
1726: exit when assignments%NOTFOUND;
1727:
1728: hr_utility.set_location('l_assignment_id '||l_assignment_id,60);
1724: hr_utility.set_location('cancel_emp_apl_hire ',50);
1725: fetch assignments into p_rowid,l_assignment_id,l_asg_status_type_id; -- Bug 3564129
1726: exit when assignments%NOTFOUND;
1727:
1728: hr_utility.set_location('l_assignment_id '||l_assignment_id,60);
1729:
1730: hr_utility.set_location('cancel_emp_apl_hire ',70);
1731:
1732: -- VT #438579 03/05/97 added delete
1726: exit when assignments%NOTFOUND;
1727:
1728: hr_utility.set_location('l_assignment_id '||l_assignment_id,60);
1729:
1730: hr_utility.set_location('cancel_emp_apl_hire ',70);
1731:
1732: -- VT #438579 03/05/97 added delete
1733:
1734: delete from per_spinal_point_placements_f spp
1736:
1737: delete from pay_cost_allocations_f pca
1738: where pca.assignment_id = l_assignment_id;
1739:
1740: hr_utility.set_location('cancel_emp_apl_hire ',80);
1741: -- Start of fix 3564129
1742:
1743: open irc_asg_status;
1744: fetch irc_asg_status into l_asg_status_id, l_asg_status_ovn;
1750: --
1751: end if;
1752: close irc_asg_status;
1753: -- fix for bug 5005157 starts here.
1754: hr_utility.set_location('cancel_emp_apl_hire ',90);
1755: open pay_proposals2(l_assignment_id);
1756: <
1757: loop
1758: fetch pay_proposals2 into l_pk_id, l_ovn;
1763: ,p_validate => FALSE
1764: ,p_salary_warning => l_sal_warning);
1765: end loop pay_proposals;
1766: close pay_proposals2;
1767: hr_utility.set_location('cancel_emp_apl_hire ',100);
1768: open csr_get_salary(l_assignment_id);
1769: fetch csr_get_salary into l_element_entry_id;
1770: if csr_get_salary%found then
1771: close csr_get_salary;
1791: close csr_get_salary;
1792: end if;
1793:
1794:
1795: hr_utility.set_location('cancel_emp_apl_hire ',110);
1796:
1797: delete from per_all_assignments_f paf
1798: where paf.assignment_id=l_assignment_id
1799: and paf.person_id=p_person_id
1798: where paf.assignment_id=l_assignment_id
1799: and paf.person_id=p_person_id
1800: and paf.effective_start_date=p_date_start;
1801:
1802: hr_utility.set_location('cancel_emp_apl_hire ',120);
1803:
1804:
1805: update per_all_assignments_f
1806: set effective_end_date = p_end_of_time
1807: where assignment_id=l_assignment_id
1808: and person_id=p_person_id
1809: and effective_end_date= p_date_start -1;
1810:
1811: hr_utility.set_location('cancel_emp_apl_hire ',121);
1812: hr_utility.set_location('l_assignment_id :'||l_assignment_id,121);
1813:
1814: if per_otherbg_apl_api.isMultiRegVac(l_assignment_id) then
1815: per_otherbg_apl_api.open_otherbg_applications(l_assignment_id,p_date_start,'CANCEL_HIRE');
1808: and person_id=p_person_id
1809: and effective_end_date= p_date_start -1;
1810:
1811: hr_utility.set_location('cancel_emp_apl_hire ',121);
1812: hr_utility.set_location('l_assignment_id :'||l_assignment_id,121);
1813:
1814: if per_otherbg_apl_api.isMultiRegVac(l_assignment_id) then
1815: per_otherbg_apl_api.open_otherbg_applications(l_assignment_id,p_date_start,'CANCEL_HIRE');
1816: end if;
1818:
1819: end loop assignment;
1820: close assignments;
1821:
1822: hr_utility.set_location('cancel_emp_apl_hire ',130);
1823:
1824: open applications;
1825: <
1826: loop
1824: open applications;
1825: <
1826: loop
1827:
1828: hr_utility.set_location('cancel_emp_apl_hire ',140);
1829: fetch applications into p_rowid;
1830:
1831: exit when applications%NOTFOUND;
1832:
1834: set pap.date_end = NULL
1835: where pap.rowid = p_rowid;
1836:
1837: if sql%notfound then
1838: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1839: hr_utility.set_message_token('PROCEDURE','cancel_hire_or_apl');
1840: hr_utility.set_message_token('STEP',11);
1841: hr_utility.raise_error;
1842: end if;
1835: where pap.rowid = p_rowid;
1836:
1837: if sql%notfound then
1838: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1839: hr_utility.set_message_token('PROCEDURE','cancel_hire_or_apl');
1840: hr_utility.set_message_token('STEP',11);
1841: hr_utility.raise_error;
1842: end if;
1843: end loop application;
1836:
1837: if sql%notfound then
1838: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1839: hr_utility.set_message_token('PROCEDURE','cancel_hire_or_apl');
1840: hr_utility.set_message_token('STEP',11);
1841: hr_utility.raise_error;
1842: end if;
1843: end loop application;
1844: close applications;
1837: if sql%notfound then
1838: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1839: hr_utility.set_message_token('PROCEDURE','cancel_hire_or_apl');
1840: hr_utility.set_message_token('STEP',11);
1841: hr_utility.raise_error;
1842: end if;
1843: end loop application;
1844: close applications;
1845:
1842: end if;
1843: end loop application;
1844: close applications;
1845:
1846: hr_utility.set_location('cancel_emp_apl_hire ',150);
1847:
1848: open ass2;
1849: <
1850: loop
1847:
1848: open ass2;
1849: <
1850: loop
1851: hr_utility.set_location('cancel_emp_apl_hire',280);
1852: fetch ass2 into p_assignment_id,p_date_start1,p_rowid;
1853: exit when ass2%NOTFOUND;
1854: hr_utility.set_location('cancel_emp_apl_hire ',160);
1855: --
1850: loop
1851: hr_utility.set_location('cancel_emp_apl_hire',280);
1852: fetch ass2 into p_assignment_id,p_date_start1,p_rowid;
1853: exit when ass2%NOTFOUND;
1854: hr_utility.set_location('cancel_emp_apl_hire ',160);
1855: --
1856: hrentmnt.maintain_entries_asg(p_assignment_id
1857: ,p_business_group_id
1858: ,'CNCL_HIRE'
1868: --
1869: open ass1;
1870:
1871: loop
1872: hr_utility.set_location('cancel_emp_apl_hire ',280);
1873: fetch ass1 into p_assignment_id,p_date_start1;
1874: exit when ass1%NOTFOUND;
1875: hr_utility.set_location('p_assignment_id '||p_assignment_id,160);
1876: hr_utility.set_location('p_date_start1 '||p_date_start1,160);
1871: loop
1872: hr_utility.set_location('cancel_emp_apl_hire ',280);
1873: fetch ass1 into p_assignment_id,p_date_start1;
1874: exit when ass1%NOTFOUND;
1875: hr_utility.set_location('p_assignment_id '||p_assignment_id,160);
1876: hr_utility.set_location('p_date_start1 '||p_date_start1,160);
1877:
1878: update per_all_assignments_f
1879: set effective_end_date = p_end_of_time
1872: hr_utility.set_location('cancel_emp_apl_hire ',280);
1873: fetch ass1 into p_assignment_id,p_date_start1;
1874: exit when ass1%NOTFOUND;
1875: hr_utility.set_location('p_assignment_id '||p_assignment_id,160);
1876: hr_utility.set_location('p_date_start1 '||p_date_start1,160);
1877:
1878: update per_all_assignments_f
1879: set effective_end_date = p_end_of_time
1880: where assignment_id=p_assignment_id
1888:
1889: end loop;
1890: close ass1;
1891: --
1892: hr_utility.set_location('cancel_hire_or_apl ',250);
1893: -- this cursor is to delete the asg changes from hire date onwards
1894: -- and will update the primary flag to Y so that the asg data will
1895: -- retain as how it was..
1896:
1913:
1914: open person;
1915: <
1916: loop
1917: hr_utility.set_location('cancel_emp_apl_hire ',300);
1918: fetch person into p_rowid, l_person_end_date;
1919: exit when person%notfound;
1920:
1921: delete from per_people_f
1923: --
1924: end loop person;
1925: close person;
1926:
1927: hr_utility.set_location('cancel_emp_apl_hire ',350);
1928:
1929:
1930: open new_person;
1931: <
1929:
1930: open new_person;
1931: <
1932: loop
1933: hr_utility.set_location('cancel_emp_apl_hire ',370);
1934: fetch new_person into p_rowid;
1935: exit when new_person%notfound;
1936:
1937:
1934: fetch new_person into p_rowid;
1935: exit when new_person%notfound;
1936:
1937:
1938: hr_utility.set_location('cancel_emp_apl_hire ',400);
1939:
1940: update per_people_f
1941: set effective_end_date = p_end_of_time
1942: where rowid = p_rowid;
1940: update per_people_f
1941: set effective_end_date = p_end_of_time
1942: where rowid = p_rowid;
1943:
1944: hr_utility.set_location('cancel_emp_apl_hire ',450);
1945: end loop new_person;
1946: close new_person;
1947:
1948: hr_utility.set_location('cancel_emp_apl_hire ',500);
1944: hr_utility.set_location('cancel_emp_apl_hire ',450);
1945: end loop new_person;
1946: close new_person;
1947:
1948: hr_utility.set_location('cancel_emp_apl_hire ',500);
1949:
1950: -- this is to cancel the person type so that we can revert
1951: -- back to previous person type i.e emp-apl
1952: OPEN csr_get_ptu_id;
1953: FETCH csr_get_ptu_id INTO l_person_type_usage_id;
1954:
1955: IF csr_get_ptu_id%FOUND THEN
1956: --
1957: hr_utility.set_location('cancel_emp_apl_hire', 510);
1958: CLOSE csr_get_ptu_id;
1959:
1960: hr_per_type_usage_internal.cancel_emp_apl_ptu
1961: (p_effective_date => p_date_start
1966:
1967: CLOSE csr_get_ptu_id;
1968: end if;
1969:
1970: hr_utility.set_location('cancel_emp_apl_hire', 521);
1971:
1972: hr_per_type_usage_internal.cancel_emp_apl_ptu
1973: (p_effective_date => p_date_start
1974: ,p_person_id => p_person_id
1973: (p_effective_date => p_date_start
1974: ,p_person_id => p_person_id
1975: ,p_system_person_type => 'EMP');
1976:
1977: hr_utility.set_location('cancel_emp_apl_hire : END ' ,550);
1978: --
1979:
1980: update per_applications pap
1981: set pap.date_end = NULL
1989: per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);
1990: --
1991: for asg_rec in c_assignments loop
1992: --
1993: hr_utility.set_location('cancel_emp_apl_hire', 551);
1994: --
1995: hr_security_internal.add_to_person_list(
1996: p_effective_date => asg_rec.effective_start_date
1997: ,p_assignment_id => asg_rec.assignment_id);
1995: hr_security_internal.add_to_person_list(
1996: p_effective_date => asg_rec.effective_start_date
1997: ,p_assignment_id => asg_rec.assignment_id);
1998: --
1999: hr_utility.set_location('cancel_emp_apl_hire', 552);
2000: --
2001: end loop;
2002:
2003: end cancel_emp_apl_hire;
2042:
2043: l_effective_end_date date := hr_general.end_of_time;
2044: begin
2045:
2046: hr_utility.set_location('Entering Upd_Person_Type_Usage_End_Date',491);
2047:
2048: open csr_upded_person_type_usages(
2049: p_person_id => p_person_id,
2050: p_effective_date => p_effective_date,
2052:
2053: fetch csr_upded_person_type_usages into l_csr_upd_per_type_usages;
2054: if csr_upded_person_type_usages%found then
2055:
2056: hr_utility.set_location('Entering Upd_Person_Type_Usage_End_Date',492);
2057:
2058: update per_person_type_usages_f ptu
2059: set effective_end_date = l_effective_end_date
2060: where ptu.effective_end_date = p_effective_date
2064:
2065: end if;
2066: close csr_upded_person_type_usages;
2067:
2068: hr_utility.set_location('Leaving Upd_Person_Type_Usage_End_Date',493);
2069: end upd_person_type_usage_end_date;
2070:
2071: /*Procedure Added for the bug 6460093*/
2072:
2404: --
2405: return v_dummy;
2406: exception
2407: when no_data_found then
2408: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
2409: hr_utility.raise_error;
2410: end;
2411: begin
2412: --
2405: return v_dummy;
2406: exception
2407: when no_data_found then
2408: hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
2409: hr_utility.raise_error;
2410: end;
2411: begin
2412: --
2413: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',10);
2409: hr_utility.raise_error;
2410: end;
2411: begin
2412: --
2413: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',10);
2414: --
2415: l_business_group_id := p_business_group_id;-- fix for bug 5005157 .
2416: if p_period_of_service_id is null then
2417: l_period_of_service_id:= get_period_of_service(p_person_id => p_person_id
2415: l_business_group_id := p_business_group_id;-- fix for bug 5005157 .
2416: if p_period_of_service_id is null then
2417: l_period_of_service_id:= get_period_of_service(p_person_id => p_person_id
2418: ,p_start_date =>p_date_start);
2419: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',20);
2420: else
2421: l_period_of_service_id := p_period_of_service_id;
2422: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',30);
2423: end if;
2418: ,p_start_date =>p_date_start);
2419: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',20);
2420: else
2421: l_period_of_service_id := p_period_of_service_id;
2422: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',30);
2423: end if;
2424: open supervisor1;
2425: <
2426: loop
2423: end if;
2424: open supervisor1;
2425: <
2426: loop
2427: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',40);
2428: fetch supervisor1 into p_rowid;
2429: exit when supervisor1%NOTFOUND;
2430: --
2431: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',50);
2427: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',40);
2428: fetch supervisor1 into p_rowid;
2429: exit when supervisor1%NOTFOUND;
2430: --
2431: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',50);
2432: update per_all_assignments_f paf
2433: set paf.supervisor_id = NULL
2434: where paf.rowid = p_rowid;
2435: --
2433: set paf.supervisor_id = NULL
2434: where paf.rowid = p_rowid;
2435: --
2436: if sql%notfound then
2437: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2438: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2439: hr_utility.set_message_token('STEP',1);
2440: hr_utility.raise_error;
2441: end if;
2434: where paf.rowid = p_rowid;
2435: --
2436: if sql%notfound then
2437: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2438: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2439: hr_utility.set_message_token('STEP',1);
2440: hr_utility.raise_error;
2441: end if;
2442: end loop supervisor;
2435: --
2436: if sql%notfound then
2437: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2438: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2439: hr_utility.set_message_token('STEP',1);
2440: hr_utility.raise_error;
2441: end if;
2442: end loop supervisor;
2443: close supervisor1;
2436: if sql%notfound then
2437: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2438: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2439: hr_utility.set_message_token('STEP',1);
2440: hr_utility.raise_error;
2441: end if;
2442: end loop supervisor;
2443: close supervisor1;
2444: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',60);
2440: hr_utility.raise_error;
2441: end if;
2442: end loop supervisor;
2443: close supervisor1;
2444: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',60);
2445: open recruiter1;
2446: <
2447: loop
2448: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',70);
2444: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',60);
2445: open recruiter1;
2446: <
2447: loop
2448: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',70);
2449: fetch recruiter1 into p_rowid;
2450: exit when recruiter1%NOTFOUND;
2451: --
2452: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',80);
2448: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',70);
2449: fetch recruiter1 into p_rowid;
2450: exit when recruiter1%NOTFOUND;
2451: --
2452: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',80);
2453: update per_all_assignments_f paf
2454: set paf.recruiter_id = NULL
2455: where paf.rowid = p_rowid;
2456: --
2454: set paf.recruiter_id = NULL
2455: where paf.rowid = p_rowid;
2456: --
2457: if sql%notfound then
2458: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2459: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2460: hr_utility.set_message_token('STEP',2);
2461: hr_utility.raise_error;
2462: end if;
2455: where paf.rowid = p_rowid;
2456: --
2457: if sql%notfound then
2458: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2459: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2460: hr_utility.set_message_token('STEP',2);
2461: hr_utility.raise_error;
2462: end if;
2463: end loop recruiter;
2456: --
2457: if sql%notfound then
2458: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2459: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2460: hr_utility.set_message_token('STEP',2);
2461: hr_utility.raise_error;
2462: end if;
2463: end loop recruiter;
2464: close recruiter1;
2457: if sql%notfound then
2458: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2459: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2460: hr_utility.set_message_token('STEP',2);
2461: hr_utility.raise_error;
2462: end if;
2463: end loop recruiter;
2464: close recruiter1;
2465: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',90);
2461: hr_utility.raise_error;
2462: end if;
2463: end loop recruiter;
2464: close recruiter1;
2465: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',90);
2466: open events;
2467: <
2468: loop
2469: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',100);
2465: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',90);
2466: open events;
2467: <
2468: loop
2469: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',100);
2470: fetch events into p_rowid;
2471: exit when events%NOTFOUND;
2472: delete from per_bookings pb
2473: where pb.rowid = p_rowid;
2471: exit when events%NOTFOUND;
2472: delete from per_bookings pb
2473: where pb.rowid = p_rowid;
2474: --
2475: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',110);
2476: if sql%notfound then
2477: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2478: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2479: hr_utility.set_message_token('STEP',3);
2473: where pb.rowid = p_rowid;
2474: --
2475: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',110);
2476: if sql%notfound then
2477: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2478: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2479: hr_utility.set_message_token('STEP',3);
2480: hr_utility.raise_error;
2481: end if;
2474: --
2475: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',110);
2476: if sql%notfound then
2477: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2478: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2479: hr_utility.set_message_token('STEP',3);
2480: hr_utility.raise_error;
2481: end if;
2482: end loop event;
2475: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',110);
2476: if sql%notfound then
2477: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2478: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2479: hr_utility.set_message_token('STEP',3);
2480: hr_utility.raise_error;
2481: end if;
2482: end loop event;
2483: close events;
2476: if sql%notfound then
2477: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2478: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2479: hr_utility.set_message_token('STEP',3);
2480: hr_utility.raise_error;
2481: end if;
2482: end loop event;
2483: close events;
2484: --
2481: end if;
2482: end loop event;
2483: close events;
2484: --
2485: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',120);
2486: --
2487: open vacancies;
2488: <
2489: loop
2486: --
2487: open vacancies;
2488: <
2489: loop
2490: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',130);
2491: fetch vacancies into p_rowid;
2492: exit when vacancies%NOTFOUND;
2493: --
2494: update per_all_vacancies pv
2494: update per_all_vacancies pv
2495: set pv.recruiter_id = NULL
2496: where pv.rowid = p_rowid;
2497: if sql%notfound then
2498: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2499: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2500: hr_utility.set_message_token('STEP',4);
2501: hr_utility.raise_error;
2502: end if;
2495: set pv.recruiter_id = NULL
2496: where pv.rowid = p_rowid;
2497: if sql%notfound then
2498: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2499: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2500: hr_utility.set_message_token('STEP',4);
2501: hr_utility.raise_error;
2502: end if;
2503: end loop vacancy;
2496: where pv.rowid = p_rowid;
2497: if sql%notfound then
2498: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2499: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2500: hr_utility.set_message_token('STEP',4);
2501: hr_utility.raise_error;
2502: end if;
2503: end loop vacancy;
2504: close vacancies;
2497: if sql%notfound then
2498: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2499: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2500: hr_utility.set_message_token('STEP',4);
2501: hr_utility.raise_error;
2502: end if;
2503: end loop vacancy;
2504: close vacancies;
2505: --
2502: end if;
2503: end loop vacancy;
2504: close vacancies;
2505: --
2506: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',140);
2507: open requisitions1;
2508: <
2509: loop
2510: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',150);
2506: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',140);
2507: open requisitions1;
2508: <
2509: loop
2510: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',150);
2511: fetch requisitions1 into p_rowid;
2512: exit when requisitions1%NOTFOUND;
2513: update per_requisitions pr
2514: set pr.person_id = NULL
2513: update per_requisitions pr
2514: set pr.person_id = NULL
2515: where pr.rowid = p_rowid;
2516: if sql%notfound then
2517: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2518: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2519: hr_utility.set_message_token('STEP',5);
2520: hr_utility.raise_error;
2521: end if;
2514: set pr.person_id = NULL
2515: where pr.rowid = p_rowid;
2516: if sql%notfound then
2517: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2518: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2519: hr_utility.set_message_token('STEP',5);
2520: hr_utility.raise_error;
2521: end if;
2522: end loop requisition;
2515: where pr.rowid = p_rowid;
2516: if sql%notfound then
2517: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2518: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2519: hr_utility.set_message_token('STEP',5);
2520: hr_utility.raise_error;
2521: end if;
2522: end loop requisition;
2523: close requisitions1;
2516: if sql%notfound then
2517: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2518: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2519: hr_utility.set_message_token('STEP',5);
2520: hr_utility.raise_error;
2521: end if;
2522: end loop requisition;
2523: close requisitions1;
2524: --
2521: end if;
2522: end loop requisition;
2523: close requisitions1;
2524: --
2525: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',160);
2526: open absenses1;
2527: <
2528: loop
2529: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',170);
2525: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',160);
2526: open absenses1;
2527: <
2528: loop
2529: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',170);
2530: fetch absenses1 into p_rowid;
2531: exit when absenses1%NOTFOUND;
2532: delete from per_absence_attendances paa
2533: where paa.rowid = p_rowid;
2531: exit when absenses1%NOTFOUND;
2532: delete from per_absence_attendances paa
2533: where paa.rowid = p_rowid;
2534: if sql%notfound then
2535: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2536: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2537: hr_utility.set_message_token('STEP',6);
2538: hr_utility.raise_error;
2539: end if;
2532: delete from per_absence_attendances paa
2533: where paa.rowid = p_rowid;
2534: if sql%notfound then
2535: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2536: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2537: hr_utility.set_message_token('STEP',6);
2538: hr_utility.raise_error;
2539: end if;
2540: end loop absense;
2533: where paa.rowid = p_rowid;
2534: if sql%notfound then
2535: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2536: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2537: hr_utility.set_message_token('STEP',6);
2538: hr_utility.raise_error;
2539: end if;
2540: end loop absense;
2541: close absenses1;
2534: if sql%notfound then
2535: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2536: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2537: hr_utility.set_message_token('STEP',6);
2538: hr_utility.raise_error;
2539: end if;
2540: end loop absense;
2541: close absenses1;
2542: --
2539: end if;
2540: end loop absense;
2541: close absenses1;
2542: --
2543: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',180);
2544: open budget_values1;
2545: <
2546: loop
2547: fetch budget_values1 into p_rowid;
2548: exit when budget_values1%NOTFOUND;
2549: delete from per_assignment_budget_values_f pab
2550: where pab.rowid = p_rowid;
2551: if sql%notfound then
2552: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2553: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2554: hr_utility.set_message_token('STEP',7);
2555: hr_utility.raise_error;
2556: end if;
2549: delete from per_assignment_budget_values_f pab
2550: where pab.rowid = p_rowid;
2551: if sql%notfound then
2552: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2553: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2554: hr_utility.set_message_token('STEP',7);
2555: hr_utility.raise_error;
2556: end if;
2557: end loop budget_value;
2550: where pab.rowid = p_rowid;
2551: if sql%notfound then
2552: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2553: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2554: hr_utility.set_message_token('STEP',7);
2555: hr_utility.raise_error;
2556: end if;
2557: end loop budget_value;
2558: close budget_values1;
2551: if sql%notfound then
2552: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2553: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2554: hr_utility.set_message_token('STEP',7);
2555: hr_utility.raise_error;
2556: end if;
2557: end loop budget_value;
2558: close budget_values1;
2559: --
2556: end if;
2557: end loop budget_value;
2558: close budget_values1;
2559: --
2560: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',190);
2561: open payment1;
2562: <
2563: loop
2564: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',200);
2560: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',190);
2561: open payment1;
2562: <
2563: loop
2564: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',200);
2565: fetch payment1 into p_rowid;
2566: exit when payment1%NOTFOUND;
2567: delete from pay_personal_payment_methods ppm
2568: where ppm.rowid = p_rowid;
2566: exit when payment1%NOTFOUND;
2567: delete from pay_personal_payment_methods ppm
2568: where ppm.rowid = p_rowid;
2569: if sql%notfound then
2570: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2571: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2572: hr_utility.set_message_token('STEP',8);
2573: hr_utility.raise_error;
2574: end if;
2567: delete from pay_personal_payment_methods ppm
2568: where ppm.rowid = p_rowid;
2569: if sql%notfound then
2570: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2571: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2572: hr_utility.set_message_token('STEP',8);
2573: hr_utility.raise_error;
2574: end if;
2575: end loop pay;
2568: where ppm.rowid = p_rowid;
2569: if sql%notfound then
2570: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2571: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2572: hr_utility.set_message_token('STEP',8);
2573: hr_utility.raise_error;
2574: end if;
2575: end loop pay;
2576: close payment1;
2569: if sql%notfound then
2570: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2571: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2572: hr_utility.set_message_token('STEP',8);
2573: hr_utility.raise_error;
2574: end if;
2575: end loop pay;
2576: close payment1;
2577: --
2574: end if;
2575: end loop pay;
2576: close payment1;
2577: --
2578: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',210);
2579: open comments1;
2580: <
2581: loop
2582: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',220);
2578: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',210);
2579: open comments1;
2580: <
2581: loop
2582: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',220);
2583: fetch comments1 into p_rowid;
2584: exit when comments1%NOTFOUND;
2585: delete from hr_comments h
2586: where h.rowid = p_rowid;
2584: exit when comments1%NOTFOUND;
2585: delete from hr_comments h
2586: where h.rowid = p_rowid;
2587: if sql%notfound then
2588: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2589: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2590: hr_utility.set_message_token('STEP',9);
2591: hr_utility.raise_error;
2592: end if;
2585: delete from hr_comments h
2586: where h.rowid = p_rowid;
2587: if sql%notfound then
2588: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2589: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2590: hr_utility.set_message_token('STEP',9);
2591: hr_utility.raise_error;
2592: end if;
2593: end loop comment;
2586: where h.rowid = p_rowid;
2587: if sql%notfound then
2588: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2589: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2590: hr_utility.set_message_token('STEP',9);
2591: hr_utility.raise_error;
2592: end if;
2593: end loop comment;
2594: close comments1;
2587: if sql%notfound then
2588: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2589: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2590: hr_utility.set_message_token('STEP',9);
2591: hr_utility.raise_error;
2592: end if;
2593: end loop comment;
2594: close comments1;
2595: --
2592: end if;
2593: end loop comment;
2594: close comments1;
2595: --
2596: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',230);
2597: -- delete the alus before performing the following operation on the
2598: -- asg data so that we dont lose the child (element entries ) records
2599: -- fix for the bug 7415677
2600: open csr_alus;
2599: -- fix for the bug 7415677
2600: open csr_alus;
2601:
2602: loop
2603: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',282);
2604: fetch csr_alus into p_assignment_id,p_start_date,p_rowid;
2605: exit when csr_alus%NOTFOUND;
2606: hr_utility.set_location('p_assignment_id ' ||p_assignment_id,280);
2607: hr_utility.set_location('p_start_date ' ||p_start_date,280);
2602: loop
2603: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',282);
2604: fetch csr_alus into p_assignment_id,p_start_date,p_rowid;
2605: exit when csr_alus%NOTFOUND;
2606: hr_utility.set_location('p_assignment_id ' ||p_assignment_id,280);
2607: hr_utility.set_location('p_start_date ' ||p_start_date,280);
2608: hrentmnt.maintain_entries_asg(p_assignment_id
2609: ,p_business_group_id
2610: ,'CNCL_HIRE'
2603: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',282);
2604: fetch csr_alus into p_assignment_id,p_start_date,p_rowid;
2605: exit when csr_alus%NOTFOUND;
2606: hr_utility.set_location('p_assignment_id ' ||p_assignment_id,280);
2607: hr_utility.set_location('p_start_date ' ||p_start_date,280);
2608: hrentmnt.maintain_entries_asg(p_assignment_id
2609: ,p_business_group_id
2610: ,'CNCL_HIRE'
2611: ,NULL
2617: --
2618: end loop;
2619: close csr_alus;
2620:
2621: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',283);
2622: --
2623: -- fix for the bug 7578210 .
2624: --
2625: open assignments;
2625: open assignments;
2626: <
2627: loop
2628: -- VT #438579 03/05/79 added assignment_id
2629: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',240);
2630: fetch assignments into p_rowid,l_assignment_id,l_asg_status_type_id; -- Bug 3564129
2631: exit when assignments%NOTFOUND;
2632: -- VT #438579 03/05/97 added delete
2633: delete from per_spinal_point_placements_f spp
2689: -- End of fix 3564129
2690: delete from per_all_assignments_f paf
2691: where paf.rowid = p_rowid;
2692: if sql%notfound then
2693: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2694: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2695: hr_utility.set_message_token('STEP',10);
2696: hr_utility.raise_error;
2697: end if;
2690: delete from per_all_assignments_f paf
2691: where paf.rowid = p_rowid;
2692: if sql%notfound then
2693: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2694: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2695: hr_utility.set_message_token('STEP',10);
2696: hr_utility.raise_error;
2697: end if;
2698: end loop assignment;
2691: where paf.rowid = p_rowid;
2692: if sql%notfound then
2693: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2694: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2695: hr_utility.set_message_token('STEP',10);
2696: hr_utility.raise_error;
2697: end if;
2698: end loop assignment;
2699: close assignments;
2692: if sql%notfound then
2693: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2694: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2695: hr_utility.set_message_token('STEP',10);
2696: hr_utility.raise_error;
2697: end if;
2698: end loop assignment;
2699: close assignments;
2700: --
2697: end if;
2698: end loop assignment;
2699: close assignments;
2700: --
2701: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',250);
2702: open applications;
2703: <
2704: loop
2705: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',260);
2701: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',250);
2702: open applications;
2703: <
2704: loop
2705: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',260);
2706: fetch applications into p_rowid;
2707: exit when applications%NOTFOUND;
2708: update per_applications pap
2709: set pap.date_end = NULL
2708: update per_applications pap
2709: set pap.date_end = NULL
2710: where pap.rowid = p_rowid;
2711: if sql%notfound then
2712: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2713: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2714: hr_utility.set_message_token('STEP',11);
2715: hr_utility.raise_error;
2716: end if;
2709: set pap.date_end = NULL
2710: where pap.rowid = p_rowid;
2711: if sql%notfound then
2712: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2713: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2714: hr_utility.set_message_token('STEP',11);
2715: hr_utility.raise_error;
2716: end if;
2717: end loop application;
2710: where pap.rowid = p_rowid;
2711: if sql%notfound then
2712: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2713: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2714: hr_utility.set_message_token('STEP',11);
2715: hr_utility.raise_error;
2716: end if;
2717: end loop application;
2718: close applications;
2711: if sql%notfound then
2712: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2713: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2714: hr_utility.set_message_token('STEP',11);
2715: hr_utility.raise_error;
2716: end if;
2717: end loop application;
2718: close applications;
2719: --
2716: end if;
2717: end loop application;
2718: close applications;
2719: --
2720: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',270);
2721: open ass1;
2722: <
2723: loop
2724: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',280);
2720: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',270);
2721: open ass1;
2722: <
2723: loop
2724: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',280);
2725: fetch ass1 into p_assignment_id,p_start_date,p_rowid;
2726: exit when ass1%NOTFOUND;
2727: update per_all_assignments_f paf
2728: set paf.effective_end_date = p_end_of_time
2746: --
2747: end loop entries;
2748: close ass1;
2749: --
2750: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',290);
2751: l_back2back := per_periods_of_service_pkg_v2.IsBackToBackContract
2752: ( p_person_id => p_person_id, p_hire_date_of_current_pds => p_date_start);
2753:
2754: open period;
2753:
2754: open period;
2755: <
2756: loop
2757: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',300);
2758: fetch period into p_rowid;
2759: exit when period%notfound;
2760: delete from per_periods_of_service
2761: where rowid = p_rowid;
2759: exit when period%notfound;
2760: delete from per_periods_of_service
2761: where rowid = p_rowid;
2762: if sql%notfound then
2763: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2764: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2765: hr_utility.set_message_token('STEP',11);
2766: hr_utility.raise_error;
2767: end if;
2760: delete from per_periods_of_service
2761: where rowid = p_rowid;
2762: if sql%notfound then
2763: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2764: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2765: hr_utility.set_message_token('STEP',11);
2766: hr_utility.raise_error;
2767: end if;
2768: end loop service;
2761: where rowid = p_rowid;
2762: if sql%notfound then
2763: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2764: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2765: hr_utility.set_message_token('STEP',11);
2766: hr_utility.raise_error;
2767: end if;
2768: end loop service;
2769: if period%rowcount <1 then
2762: if sql%notfound then
2763: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2764: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2765: hr_utility.set_message_token('STEP',11);
2766: hr_utility.raise_error;
2767: end if;
2768: end loop service;
2769: if period%rowcount <1 then
2770: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2766: hr_utility.raise_error;
2767: end if;
2768: end loop service;
2769: if period%rowcount <1 then
2770: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2771: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2772: hr_utility.set_message_token('STEP',12);
2773: hr_utility.raise_error;
2774: end if;
2767: end if;
2768: end loop service;
2769: if period%rowcount <1 then
2770: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2771: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2772: hr_utility.set_message_token('STEP',12);
2773: hr_utility.raise_error;
2774: end if;
2775: close period;
2768: end loop service;
2769: if period%rowcount <1 then
2770: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2771: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2772: hr_utility.set_message_token('STEP',12);
2773: hr_utility.raise_error;
2774: end if;
2775: close period;
2776: --
2769: if period%rowcount <1 then
2770: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2771: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2772: hr_utility.set_message_token('STEP',12);
2773: hr_utility.raise_error;
2774: end if;
2775: close period;
2776: --
2777: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',310);
2773: hr_utility.raise_error;
2774: end if;
2775: close period;
2776: --
2777: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',310);
2778: -- 3194314
2779: open csr_is_cwk(p_date_start);
2780: fetch csr_is_cwk into l_pp_id, l_cwk_date_start;
2781: close csr_is_cwk;
2778: -- 3194314
2779: open csr_is_cwk(p_date_start);
2780: fetch csr_is_cwk into l_pp_id, l_cwk_date_start;
2781: close csr_is_cwk;
2782: hr_utility.trace('CWK pp_id = '||l_pp_id);
2783: --
2784: -- 3848352 start
2785: l_apl_flag := NULL;
2786: open csr_apl_ptu_id;
2788: if csr_apl_ptu_id%found then
2789: l_apl_flag := 'Y';
2790: end if;
2791: close csr_apl_ptu_id;
2792: hr_utility.trace('l_apl_flag = '||l_apl_flag);
2793: -- 3848352 end
2794:
2795: -- <<
2796: open person;
2795: -- <<
2796: open person;
2797: <
2798: loop
2799: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',320);
2800: fetch person into p_rowid, l_person_end_date;
2801: exit when person%notfound;
2802: if l_back2back
2803: then
2820: end if;
2821: -- only in per_all_people_f the person tye has to be ex_emp_apl
2822: -- code added for the bug 8504597 ends here
2823:
2824: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',322);
2825:
2826: update per_people_f
2827: set person_type_id = l_person_type_id,
2828: effective_start_date = p_date_start, -- in case DT updates exist
2851: --
2852:
2853: else -- #1998140
2854: --
2855: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',323);
2856:
2857: delete from per_people_f
2858: where rowid = p_rowid;
2859: --
2893: -- end changes for bug 8405711
2894: end if;
2895: -- 3848352 end
2896:
2897: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',324);
2898:
2899: --
2900: -- 3847884 Changed from p_start_date to p_date_start
2901: --
2912: where rowid = p_rowid;
2913: --
2914: else -- #1998140
2915: --
2916: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',325);
2917:
2918: delete from per_people_f
2919: where rowid = p_rowid;
2920: --
2921: end if;
2922:
2923: -- <<
2924: else
2925: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',330);
2926: delete from per_people_f
2927: where rowid = p_rowid;
2928: end if;
2929: --
2926: delete from per_people_f
2927: where rowid = p_rowid;
2928: end if;
2929: --
2930: -- hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',340); -- for bug 14523191
2931: -- if sql%ROWCOUNT <1 then
2932: if sql%notfound then
2933: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2934: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2929: --
2930: -- hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',340); -- for bug 14523191
2931: -- if sql%ROWCOUNT <1 then
2932: if sql%notfound then
2933: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2934: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2935: hr_utility.set_message_token('STEP',13);
2936: hr_utility.raise_error;
2937: end if;
2930: -- hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',340); -- for bug 14523191
2931: -- if sql%ROWCOUNT <1 then
2932: if sql%notfound then
2933: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2934: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2935: hr_utility.set_message_token('STEP',13);
2936: hr_utility.raise_error;
2937: end if;
2938: end loop per;
2931: -- if sql%ROWCOUNT <1 then
2932: if sql%notfound then
2933: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2934: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2935: hr_utility.set_message_token('STEP',13);
2936: hr_utility.raise_error;
2937: end if;
2938: end loop per;
2939: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
2932: if sql%notfound then
2933: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2934: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2935: hr_utility.set_message_token('STEP',13);
2936: hr_utility.raise_error;
2937: end if;
2938: end loop per;
2939: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
2940: if person%rowcount <1 then
2935: hr_utility.set_message_token('STEP',13);
2936: hr_utility.raise_error;
2937: end if;
2938: end loop per;
2939: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
2940: if person%rowcount <1 then
2941: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2942: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2943: hr_utility.set_message_token('STEP',14);
2937: end if;
2938: end loop per;
2939: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
2940: if person%rowcount <1 then
2941: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2942: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2943: hr_utility.set_message_token('STEP',14);
2944: hr_utility.raise_error;
2945: end if;
2938: end loop per;
2939: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
2940: if person%rowcount <1 then
2941: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2942: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2943: hr_utility.set_message_token('STEP',14);
2944: hr_utility.raise_error;
2945: end if;
2946: close person;
2939: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
2940: if person%rowcount <1 then
2941: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2942: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2943: hr_utility.set_message_token('STEP',14);
2944: hr_utility.raise_error;
2945: end if;
2946: close person;
2947: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',360);
2940: if person%rowcount <1 then
2941: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2942: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2943: hr_utility.set_message_token('STEP',14);
2944: hr_utility.raise_error;
2945: end if;
2946: close person;
2947: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',360);
2948: -- 3194314
2943: hr_utility.set_message_token('STEP',14);
2944: hr_utility.raise_error;
2945: end if;
2946: close person;
2947: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',360);
2948: -- 3194314
2949: -- this should not get exected when b2b cwk/emp
2950: if l_pp_id is null then
2951: open new_person;
2950: if l_pp_id is null then
2951: open new_person;
2952: <
2953: loop
2954: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',370);
2955: fetch new_person into p_rowid;
2956: exit when new_person%notfound;
2957: if NOT l_back2back
2958: then
2955: fetch new_person into p_rowid;
2956: exit when new_person%notfound;
2957: if NOT l_back2back
2958: then
2959: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',380);
2960: update per_people_f
2961: set effective_end_date = p_end_of_time
2962: where rowid = p_rowid;
2963: end if;
2961: set effective_end_date = p_end_of_time
2962: where rowid = p_rowid;
2963: end if;
2964: --
2965: -- hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',390); -- for bug 14523191
2966: if sql%ROWCOUNT <1 then
2967: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2968: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2969: hr_utility.set_message_token('STEP',15);
2963: end if;
2964: --
2965: -- hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',390); -- for bug 14523191
2966: if sql%ROWCOUNT <1 then
2967: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2968: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2969: hr_utility.set_message_token('STEP',15);
2970: hr_utility.raise_error;
2971: end if;
2964: --
2965: -- hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',390); -- for bug 14523191
2966: if sql%ROWCOUNT <1 then
2967: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2968: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2969: hr_utility.set_message_token('STEP',15);
2970: hr_utility.raise_error;
2971: end if;
2972: end loop new_per;
2965: -- hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',390); -- for bug 14523191
2966: if sql%ROWCOUNT <1 then
2967: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2968: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2969: hr_utility.set_message_token('STEP',15);
2970: hr_utility.raise_error;
2971: end if;
2972: end loop new_per;
2973: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',400);
2966: if sql%ROWCOUNT <1 then
2967: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2968: hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2969: hr_utility.set_message_token('STEP',15);
2970: hr_utility.raise_error;
2971: end if;
2972: end loop new_per;
2973: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',400);
2974: if new_person%rowcount <1 then
2969: hr_utility.set_message_token('STEP',15);
2970: hr_utility.raise_error;
2971: end if;
2972: end loop new_per;
2973: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',400);
2974: if new_person%rowcount <1 then
2975: --Fix for bug 9371186 starts here
2976:
2977: -- hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2973: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',400);
2974: if new_person%rowcount <1 then
2975: --Fix for bug 9371186 starts here
2976:
2977: -- hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2978: -- hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2979: -- hr_utility.set_message_token('STEP',16);
2980:
2981: hr_utility.set_message(800,'PER_449888_NO_PRE_PER_TYPE');
2974: if new_person%rowcount <1 then
2975: --Fix for bug 9371186 starts here
2976:
2977: -- hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2978: -- hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2979: -- hr_utility.set_message_token('STEP',16);
2980:
2981: hr_utility.set_message(800,'PER_449888_NO_PRE_PER_TYPE');
2982: --Fix for bug 9371186 ends here
2975: --Fix for bug 9371186 starts here
2976:
2977: -- hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2978: -- hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2979: -- hr_utility.set_message_token('STEP',16);
2980:
2981: hr_utility.set_message(800,'PER_449888_NO_PRE_PER_TYPE');
2982: --Fix for bug 9371186 ends here
2983: hr_utility.raise_error;
2977: -- hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2978: -- hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2979: -- hr_utility.set_message_token('STEP',16);
2980:
2981: hr_utility.set_message(800,'PER_449888_NO_PRE_PER_TYPE');
2982: --Fix for bug 9371186 ends here
2983: hr_utility.raise_error;
2984: end if;
2985: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',410);
2979: -- hr_utility.set_message_token('STEP',16);
2980:
2981: hr_utility.set_message(800,'PER_449888_NO_PRE_PER_TYPE');
2982: --Fix for bug 9371186 ends here
2983: hr_utility.raise_error;
2984: end if;
2985: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',410);
2986: close new_person;
2987:
2981: hr_utility.set_message(800,'PER_449888_NO_PRE_PER_TYPE');
2982: --Fix for bug 9371186 ends here
2983: hr_utility.raise_error;
2984: end if;
2985: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',410);
2986: close new_person;
2987:
2988: end if; -- 3194314 is b2b?
2989: --
2986: close new_person;
2987:
2988: end if; -- 3194314 is b2b?
2989: --
2990: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',420);
2991: -- 115.1 Change by M Bocutt
2992: --
2993: -- If we have an EMP PTU record that started on the hire date
2994: -- then delete it.
2994: -- then delete it.
2995: --
2996: -- PTU : Code added
2997:
2998: hr_utility.set_location('cancel_hire_or_apl.p_date_start = '||to_char(p_date_start,'DD/MM/YYYY'),420);
2999: hr_utility.set_location('cancel_hire_or_apl.p_person_id = '||to_char(p_person_id),420);
3000:
3001: if l_pp_id is not null then -- 3194314
3002:
2995: --
2996: -- PTU : Code added
2997:
2998: hr_utility.set_location('cancel_hire_or_apl.p_date_start = '||to_char(p_date_start,'DD/MM/YYYY'),420);
2999: hr_utility.set_location('cancel_hire_or_apl.p_person_id = '||to_char(p_person_id),420);
3000:
3001: if l_pp_id is not null then -- 3194314
3002:
3003: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',424);
2999: hr_utility.set_location('cancel_hire_or_apl.p_person_id = '||to_char(p_person_id),420);
3000:
3001: if l_pp_id is not null then -- 3194314
3002:
3003: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',424);
3004:
3005: open csr_emp_ptu_id;
3006: fetch csr_emp_ptu_id into l_emp_ptu_id;
3007: if csr_emp_ptu_id%FOUND and l_emp_ptu_id is not null then
3005: open csr_emp_ptu_id;
3006: fetch csr_emp_ptu_id into l_emp_ptu_id;
3007: if csr_emp_ptu_id%FOUND and l_emp_ptu_id is not null then
3008:
3009: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',425);
3010: --fix for bug 6671352 starts here.
3011: /*hr_per_type_usage_internal.maintain_person_type_usage
3012: (p_effective_date => p_date_start
3013: ,p_person_id => p_person_id
3028: open csr_apl_ptu_id;
3029: fetch csr_apl_ptu_id into l_apl_ptu_id;
3030: if csr_apl_ptu_id%FOUND and l_apl_ptu_id is not null then
3031:
3032: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',425);
3033: hr_per_type_usage_internal.maintain_person_type_usage
3034: (p_effective_date => p_date_start -1
3035: ,p_person_id => p_person_id
3036: ,p_person_type_id => l_apl_ptu_id
3060: ,p_system_person_type => 'OTHER');
3061:
3062: -- Change for the bug 6899072 ends here
3063:
3064: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',430);
3065: --
3066: --5450847 start : Need to maintain APL record if any. Delete the EX_APL
3067: -- record and update EED of APL record to EOTime.
3068: --
3069: open csr_apl_ptu_id;
3070: fetch csr_apl_ptu_id into l_apl_ptu_id;
3071: if csr_apl_ptu_id%FOUND and l_apl_ptu_id is not null then
3072:
3073: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',432);
3074: hr_per_type_usage_internal.maintain_person_type_usage
3075: (p_effective_date => p_date_start -1
3076: ,p_person_id => p_person_id
3077: ,p_person_type_id => l_apl_ptu_id
3078: ,p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE'
3079: );
3080: end if;
3081: close csr_apl_ptu_id;
3082: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',433);
3083: --
3084: -- 5450847 end
3085: --
3086:
3087: end if;
3088:
3089: if l_back2back then
3090:
3091: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',435);
3092:
3093: hr_per_type_usage_internal.maintain_person_type_usage
3094: (p_effective_date => p_date_start
3095: ,p_person_id => p_person_id
3114: per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);
3115: --
3116: for asg_rec in c_assignments loop
3117: --
3118: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire', 501);
3119: --
3120: hr_security_internal.add_to_person_list(
3121: p_effective_date => asg_rec.effective_start_date
3122: ,p_assignment_id => asg_rec.assignment_id);
3120: hr_security_internal.add_to_person_list(
3121: p_effective_date => asg_rec.effective_start_date
3122: ,p_assignment_id => asg_rec.assignment_id);
3123: --
3124: hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire', 502);
3125: --
3126: end loop;
3127: --
3128: end do_cancel_hire;
3429: exit when events_or_interviews%NOTFOUND;
3430: delete from per_events pe
3431: where pe.event_id = l_pk_id;
3432: if sql%notfound then
3433: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3434: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3435: hr_utility.set_message_token('STEP',2);
3436: hr_utility.raise_error;
3437: end if;
3430: delete from per_events pe
3431: where pe.event_id = l_pk_id;
3432: if sql%notfound then
3433: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3434: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3435: hr_utility.set_message_token('STEP',2);
3436: hr_utility.raise_error;
3437: end if;
3438: end loop event;
3431: where pe.event_id = l_pk_id;
3432: if sql%notfound then
3433: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3434: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3435: hr_utility.set_message_token('STEP',2);
3436: hr_utility.raise_error;
3437: end if;
3438: end loop event;
3439: close events_or_interviews;
3432: if sql%notfound then
3433: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3434: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3435: hr_utility.set_message_token('STEP',2);
3436: hr_utility.raise_error;
3437: end if;
3438: end loop event;
3439: close events_or_interviews;
3440: --
3446: exit when budget_values2%NOTFOUND;
3447: delete from per_assignment_budget_values_f pab
3448: where pab.rowid = l_rowid;
3449: if sql%notfound then
3450: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3451: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3452: hr_utility.set_message_token('STEP',3);
3453: hr_utility.raise_error;
3454: end if;
3447: delete from per_assignment_budget_values_f pab
3448: where pab.rowid = l_rowid;
3449: if sql%notfound then
3450: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3451: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3452: hr_utility.set_message_token('STEP',3);
3453: hr_utility.raise_error;
3454: end if;
3455: end loop budget_val;
3448: where pab.rowid = l_rowid;
3449: if sql%notfound then
3450: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3451: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3452: hr_utility.set_message_token('STEP',3);
3453: hr_utility.raise_error;
3454: end if;
3455: end loop budget_val;
3456: close budget_values2;
3449: if sql%notfound then
3450: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3451: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3452: hr_utility.set_message_token('STEP',3);
3453: hr_utility.raise_error;
3454: end if;
3455: end loop budget_val;
3456: close budget_values2;
3457: --
3463: exit when comments2%NOTFOUND;
3464: delete from hr_comments h
3465: where h.comment_id = l_pk_id;
3466: if sql%notfound then
3467: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3468: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3469: hr_utility.set_message_token('STEP',4);
3470: hr_utility.raise_error;
3471: end if;
3464: delete from hr_comments h
3465: where h.comment_id = l_pk_id;
3466: if sql%notfound then
3467: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3468: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3469: hr_utility.set_message_token('STEP',4);
3470: hr_utility.raise_error;
3471: end if;
3472: end loop comments;
3465: where h.comment_id = l_pk_id;
3466: if sql%notfound then
3467: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3468: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3469: hr_utility.set_message_token('STEP',4);
3470: hr_utility.raise_error;
3471: end if;
3472: end loop comments;
3473: close comments2;
3466: if sql%notfound then
3467: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3468: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3469: hr_utility.set_message_token('STEP',4);
3470: hr_utility.raise_error;
3471: end if;
3472: end loop comments;
3473: close comments2;
3474: --
3480: exit when letters2%NOTFOUND;
3481: delete from per_letter_request_lines plrl
3482: where plrl.letter_request_line_id = l_pk_id;
3483: if sql%notfound then
3484: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3485: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3486: hr_utility.set_message_token('STEP',5);
3487: hr_utility.raise_error;
3488: end if;
3481: delete from per_letter_request_lines plrl
3482: where plrl.letter_request_line_id = l_pk_id;
3483: if sql%notfound then
3484: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3485: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3486: hr_utility.set_message_token('STEP',5);
3487: hr_utility.raise_error;
3488: end if;
3489: end loop letters;
3482: where plrl.letter_request_line_id = l_pk_id;
3483: if sql%notfound then
3484: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3485: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3486: hr_utility.set_message_token('STEP',5);
3487: hr_utility.raise_error;
3488: end if;
3489: end loop letters;
3490: close letters2;
3483: if sql%notfound then
3484: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3485: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3486: hr_utility.set_message_token('STEP',5);
3487: hr_utility.raise_error;
3488: end if;
3489: end loop letters;
3490: close letters2;
3491: --
3547: exit when asg_prop_answer2%NOTFOUND;
3548: delete from per_assign_proposal_answers papa
3549: where papa.rowid = l_rowid;
3550: if sql%notfound then
3551: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3552: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3553: hr_utility.set_message_token('STEP',6);
3554: hr_utility.raise_error;
3555: end if;
3548: delete from per_assign_proposal_answers papa
3549: where papa.rowid = l_rowid;
3550: if sql%notfound then
3551: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3552: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3553: hr_utility.set_message_token('STEP',6);
3554: hr_utility.raise_error;
3555: end if;
3556: end loop asg_prop_answer;
3549: where papa.rowid = l_rowid;
3550: if sql%notfound then
3551: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3552: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3553: hr_utility.set_message_token('STEP',6);
3554: hr_utility.raise_error;
3555: end if;
3556: end loop asg_prop_answer;
3557: close asg_prop_answer2;
3550: if sql%notfound then
3551: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3552: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3553: hr_utility.set_message_token('STEP',6);
3554: hr_utility.raise_error;
3555: end if;
3556: end loop asg_prop_answer;
3557: close asg_prop_answer2;
3558: --
3588: exit when bookings2%NOTFOUND;
3589: delete from per_bookings pb
3590: where pb.booking_id = l_pk_id;
3591: if sql%notfound then
3592: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3593: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3594: hr_utility.set_message_token('STEP',7);
3595: hr_utility.raise_error;
3596: end if;
3589: delete from per_bookings pb
3590: where pb.booking_id = l_pk_id;
3591: if sql%notfound then
3592: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3593: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3594: hr_utility.set_message_token('STEP',7);
3595: hr_utility.raise_error;
3596: end if;
3597: end loop bookings;
3590: where pb.booking_id = l_pk_id;
3591: if sql%notfound then
3592: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3593: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3594: hr_utility.set_message_token('STEP',7);
3595: hr_utility.raise_error;
3596: end if;
3597: end loop bookings;
3598: close bookings2;
3591: if sql%notfound then
3592: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3593: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3594: hr_utility.set_message_token('STEP',7);
3595: hr_utility.raise_error;
3596: end if;
3597: end loop bookings;
3598: close bookings2;
3599: --
3605: exit when person_apl_comments2%NOTFOUND;
3606: delete from hr_comments h
3607: where h.comment_id = l_pk_id;
3608: if sql%notfound then
3609: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3610: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3611: hr_utility.set_message_token('STEP',8);
3612: hr_utility.raise_error;
3613: end if;
3606: delete from hr_comments h
3607: where h.comment_id = l_pk_id;
3608: if sql%notfound then
3609: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3610: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3611: hr_utility.set_message_token('STEP',8);
3612: hr_utility.raise_error;
3613: end if;
3614: end loop person_apl_comments;
3607: where h.comment_id = l_pk_id;
3608: if sql%notfound then
3609: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3610: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3611: hr_utility.set_message_token('STEP',8);
3612: hr_utility.raise_error;
3613: end if;
3614: end loop person_apl_comments;
3615: close person_apl_comments2;
3608: if sql%notfound then
3609: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3610: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3611: hr_utility.set_message_token('STEP',8);
3612: hr_utility.raise_error;
3613: end if;
3614: end loop person_apl_comments;
3615: close person_apl_comments2;
3616: --
3624: exit when assignments%NOTFOUND;
3625: delete from per_assignments_f paf
3626: where paf.rowid = l_rowid;
3627: if sql%notfound then
3628: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3629: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3630: hr_utility.set_message_token('STEP',9);
3631: hr_utility.raise_error;
3632: end if;
3625: delete from per_assignments_f paf
3626: where paf.rowid = l_rowid;
3627: if sql%notfound then
3628: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3629: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3630: hr_utility.set_message_token('STEP',9);
3631: hr_utility.raise_error;
3632: end if;
3633: end loop assign;
3626: where paf.rowid = l_rowid;
3627: if sql%notfound then
3628: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3629: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3630: hr_utility.set_message_token('STEP',9);
3631: hr_utility.raise_error;
3632: end if;
3633: end loop assign;
3634: close assignments;
3627: if sql%notfound then
3628: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3629: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3630: hr_utility.set_message_token('STEP',9);
3631: hr_utility.raise_error;
3632: end if;
3633: end loop assign;
3634: close assignments;
3635: --
3641: exit when applications%NOTFOUND;
3642: delete from per_applications pap
3643: where pap.rowid = l_rowid;
3644: if sql%notfound then
3645: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3646: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3647: hr_utility.set_message_token('STEP',10);
3648: hr_utility.raise_error;
3649: end if;
3642: delete from per_applications pap
3643: where pap.rowid = l_rowid;
3644: if sql%notfound then
3645: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3646: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3647: hr_utility.set_message_token('STEP',10);
3648: hr_utility.raise_error;
3649: end if;
3650: end loop application;
3643: where pap.rowid = l_rowid;
3644: if sql%notfound then
3645: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3646: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3647: hr_utility.set_message_token('STEP',10);
3648: hr_utility.raise_error;
3649: end if;
3650: end loop application;
3651: close applications;
3644: if sql%notfound then
3645: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3646: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3647: hr_utility.set_message_token('STEP',10);
3648: hr_utility.raise_error;
3649: end if;
3650: end loop application;
3651: close applications;
3652: --
3671: exit when people%notfound;
3672: delete from per_people_f
3673: where rowid = l_rowid;
3674: if sql%notfound then
3675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3676: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3677: hr_utility.set_message_token('STEP',11);
3678: hr_utility.raise_error;
3679: end if;
3672: delete from per_people_f
3673: where rowid = l_rowid;
3674: if sql%notfound then
3675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3676: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3677: hr_utility.set_message_token('STEP',11);
3678: hr_utility.raise_error;
3679: end if;
3680: end loop per;
3673: where rowid = l_rowid;
3674: if sql%notfound then
3675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3676: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3677: hr_utility.set_message_token('STEP',11);
3678: hr_utility.raise_error;
3679: end if;
3680: end loop per;
3681: if people%rowcount <1 then
3674: if sql%notfound then
3675: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3676: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3677: hr_utility.set_message_token('STEP',11);
3678: hr_utility.raise_error;
3679: end if;
3680: end loop per;
3681: if people%rowcount <1 then
3682: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3678: hr_utility.raise_error;
3679: end if;
3680: end loop per;
3681: if people%rowcount <1 then
3682: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3683: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3684: hr_utility.set_message_token('STEP',12);
3685: hr_utility.raise_error;
3686: end if;
3679: end if;
3680: end loop per;
3681: if people%rowcount <1 then
3682: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3683: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3684: hr_utility.set_message_token('STEP',12);
3685: hr_utility.raise_error;
3686: end if;
3687: close people;
3680: end loop per;
3681: if people%rowcount <1 then
3682: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3683: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3684: hr_utility.set_message_token('STEP',12);
3685: hr_utility.raise_error;
3686: end if;
3687: close people;
3688: --
3681: if people%rowcount <1 then
3682: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3683: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3684: hr_utility.set_message_token('STEP',12);
3685: hr_utility.raise_error;
3686: end if;
3687: close people;
3688: --
3689: --
3696: set effective_end_date = p_end_of_time
3697: where rowid = l_rowid;
3698: --
3699: if sql%ROWCOUNT <1 then
3700: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3701: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3702: hr_utility.set_message_token('STEP',13);
3703: hr_utility.raise_error;
3704: end if;
3697: where rowid = l_rowid;
3698: --
3699: if sql%ROWCOUNT <1 then
3700: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3701: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3702: hr_utility.set_message_token('STEP',13);
3703: hr_utility.raise_error;
3704: end if;
3705: end loop new_per;
3698: --
3699: if sql%ROWCOUNT <1 then
3700: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3701: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3702: hr_utility.set_message_token('STEP',13);
3703: hr_utility.raise_error;
3704: end if;
3705: end loop new_per;
3706: if new_people%rowcount <1 then
3699: if sql%ROWCOUNT <1 then
3700: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3701: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3702: hr_utility.set_message_token('STEP',13);
3703: hr_utility.raise_error;
3704: end if;
3705: end loop new_per;
3706: if new_people%rowcount <1 then
3707: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3703: hr_utility.raise_error;
3704: end if;
3705: end loop new_per;
3706: if new_people%rowcount <1 then
3707: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3708: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3709: hr_utility.set_message_token('STEP',14);
3710: hr_utility.raise_error;
3711: end if;
3704: end if;
3705: end loop new_per;
3706: if new_people%rowcount <1 then
3707: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3708: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3709: hr_utility.set_message_token('STEP',14);
3710: hr_utility.raise_error;
3711: end if;
3712: close new_people;
3705: end loop new_per;
3706: if new_people%rowcount <1 then
3707: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3708: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3709: hr_utility.set_message_token('STEP',14);
3710: hr_utility.raise_error;
3711: end if;
3712: close new_people;
3713: -- Bug 4095559 Starts
3706: if new_people%rowcount <1 then
3707: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3708: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3709: hr_utility.set_message_token('STEP',14);
3710: hr_utility.raise_error;
3711: end if;
3712: close new_people;
3713: -- Bug 4095559 Starts
3714: -- Desc: : Execute the new logic, if it is B2B
3749: delete from per_people_f
3750: where rowid = l_rowid;
3751:
3752: if sql%notfound THEN
3753: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3754: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3755: hr_utility.set_message_token('STEP',15);
3756: hr_utility.raise_error;
3757: end if;
3750: where rowid = l_rowid;
3751:
3752: if sql%notfound THEN
3753: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3754: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3755: hr_utility.set_message_token('STEP',15);
3756: hr_utility.raise_error;
3757: end if;
3758: end loop per_b2b;
3751:
3752: if sql%notfound THEN
3753: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3754: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3755: hr_utility.set_message_token('STEP',15);
3756: hr_utility.raise_error;
3757: end if;
3758: end loop per_b2b;
3759: /* if people_b2b%rowcount <1 then
3752: if sql%notfound THEN
3753: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3754: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3755: hr_utility.set_message_token('STEP',15);
3756: hr_utility.raise_error;
3757: end if;
3758: end loop per_b2b;
3759: /* if people_b2b%rowcount <1 then
3760: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3756: hr_utility.raise_error;
3757: end if;
3758: end loop per_b2b;
3759: /* if people_b2b%rowcount <1 then
3760: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3761: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3762: hr_utility.set_message_token('STEP',16);
3763: hr_utility.raise_error;
3764: end if;*/
3757: end if;
3758: end loop per_b2b;
3759: /* if people_b2b%rowcount <1 then
3760: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3761: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3762: hr_utility.set_message_token('STEP',16);
3763: hr_utility.raise_error;
3764: end if;*/
3765: close people_b2b;
3758: end loop per_b2b;
3759: /* if people_b2b%rowcount <1 then
3760: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3761: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3762: hr_utility.set_message_token('STEP',16);
3763: hr_utility.raise_error;
3764: end if;*/
3765: close people_b2b;
3766: ELSE
3759: /* if people_b2b%rowcount <1 then
3760: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3761: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3762: hr_utility.set_message_token('STEP',16);
3763: hr_utility.raise_error;
3764: end if;*/
3765: close people_b2b;
3766: ELSE
3767: open people_b2b_with_prev_record;
3774: delete from per_people_f
3775: where rowid = l_rowid;
3776:
3777: if sql%notfound then
3778: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3779: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3780: hr_utility.set_message_token('STEP',15);
3781: hr_utility.raise_error;
3782: end if;
3775: where rowid = l_rowid;
3776:
3777: if sql%notfound then
3778: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3779: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3780: hr_utility.set_message_token('STEP',15);
3781: hr_utility.raise_error;
3782: end if;
3783: end loop per_b2b_with_prev_record;
3776:
3777: if sql%notfound then
3778: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3779: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3780: hr_utility.set_message_token('STEP',15);
3781: hr_utility.raise_error;
3782: end if;
3783: end loop per_b2b_with_prev_record;
3784: CLOSE people_b2b_with_prev_record;
3777: if sql%notfound then
3778: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3779: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3780: hr_utility.set_message_token('STEP',15);
3781: hr_utility.raise_error;
3782: end if;
3783: end loop per_b2b_with_prev_record;
3784: CLOSE people_b2b_with_prev_record;
3785: END IF;
3841: end if;*/
3842: --
3843: --end changes for bug 8405711
3844: if sql%notfound then
3845: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3846: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3847: hr_utility.set_message_token('STEP',17);
3848: hr_utility.raise_error;
3849: end if;
3842: --
3843: --end changes for bug 8405711
3844: if sql%notfound then
3845: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3846: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3847: hr_utility.set_message_token('STEP',17);
3848: hr_utility.raise_error;
3849: end if;
3850: end loop new_per_b2b;
3843: --end changes for bug 8405711
3844: if sql%notfound then
3845: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3846: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3847: hr_utility.set_message_token('STEP',17);
3848: hr_utility.raise_error;
3849: end if;
3850: end loop new_per_b2b;
3851: if new_people_b2b%rowcount <1 then
3844: if sql%notfound then
3845: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3846: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3847: hr_utility.set_message_token('STEP',17);
3848: hr_utility.raise_error;
3849: end if;
3850: end loop new_per_b2b;
3851: if new_people_b2b%rowcount <1 then
3852: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3848: hr_utility.raise_error;
3849: end if;
3850: end loop new_per_b2b;
3851: if new_people_b2b%rowcount <1 then
3852: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3853: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3854: hr_utility.set_message_token('STEP',18);
3855: hr_utility.raise_error;
3856: end if;
3849: end if;
3850: end loop new_per_b2b;
3851: if new_people_b2b%rowcount <1 then
3852: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3853: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3854: hr_utility.set_message_token('STEP',18);
3855: hr_utility.raise_error;
3856: end if;
3857: close new_people_b2b;
3850: end loop new_per_b2b;
3851: if new_people_b2b%rowcount <1 then
3852: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3853: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3854: hr_utility.set_message_token('STEP',18);
3855: hr_utility.raise_error;
3856: end if;
3857: close new_people_b2b;
3858:
3851: if new_people_b2b%rowcount <1 then
3852: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3853: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3854: hr_utility.set_message_token('STEP',18);
3855: hr_utility.raise_error;
3856: end if;
3857: close new_people_b2b;
3858:
3859: end if;
3877: ,p_effective_start_date => l_start_date
3878: ,p_effective_end_date => l_end_date );
3879: end loop person_type_usages;
3880: if person_type_usages2%rowcount <1 then
3881: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3882: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3883: hr_utility.set_message_token('STEP',15);
3884: hr_utility.raise_error;
3885: end if;
3878: ,p_effective_end_date => l_end_date );
3879: end loop person_type_usages;
3880: if person_type_usages2%rowcount <1 then
3881: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3882: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3883: hr_utility.set_message_token('STEP',15);
3884: hr_utility.raise_error;
3885: end if;
3886: close person_type_usages2;
3879: end loop person_type_usages;
3880: if person_type_usages2%rowcount <1 then
3881: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3882: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3883: hr_utility.set_message_token('STEP',15);
3884: hr_utility.raise_error;
3885: end if;
3886: close person_type_usages2;
3887: */
3880: if person_type_usages2%rowcount <1 then
3881: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3882: hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3883: hr_utility.set_message_token('STEP',15);
3884: hr_utility.raise_error;
3885: end if;
3886: close person_type_usages2;
3887: */
3888: -- Bug 4095559 Starts
4153:
4154: -- end changes for bug 8405711
4155: BEGIN
4156: --
4157: hr_utility.set_location('Entering : '||l_proc,10);
4158: --
4159: l_effective_date := TRUNC(p_effective_date);
4160: --
4161: -- Retrieve the person type usage ID for the CWK,
4167: IF csr_get_ptu_id%NOTFOUND THEN
4168: --
4169: CLOSE csr_get_ptu_id;
4170: --
4171: hr_utility.set_message(801,'HR_289751_CWK_SYS_PER_TYPE_ERR');
4172: hr_utility.raise_error;
4173: --
4174: END IF;
4175: --
4168: --
4169: CLOSE csr_get_ptu_id;
4170: --
4171: hr_utility.set_message(801,'HR_289751_CWK_SYS_PER_TYPE_ERR');
4172: hr_utility.raise_error;
4173: --
4174: END IF;
4175: --
4176: hr_utility.set_location(l_proc,15);
4172: hr_utility.raise_error;
4173: --
4174: END IF;
4175: --
4176: hr_utility.set_location(l_proc,15);
4177: --
4178: CLOSE csr_get_ptu_id;
4179: --
4180: -- start changes for bug 8405711
4184: fetch csr_application_change_exists into l_dummy;
4185:
4186: if csr_application_change_exists%FOUND then
4187: close csr_application_change_exists;
4188: hr_utility.set_message(800,'PER_449844_APL_ACTIONS_EXISTS');
4189: hr_utility.set_message_token('PERSON_STATUS','placement');
4190: hr_utility.set_message_token('PERSON_TYPE','Contingent Worker');
4191: hr_utility.raise_error;
4192: else
4185:
4186: if csr_application_change_exists%FOUND then
4187: close csr_application_change_exists;
4188: hr_utility.set_message(800,'PER_449844_APL_ACTIONS_EXISTS');
4189: hr_utility.set_message_token('PERSON_STATUS','placement');
4190: hr_utility.set_message_token('PERSON_TYPE','Contingent Worker');
4191: hr_utility.raise_error;
4192: else
4193: close csr_application_change_exists;
4186: if csr_application_change_exists%FOUND then
4187: close csr_application_change_exists;
4188: hr_utility.set_message(800,'PER_449844_APL_ACTIONS_EXISTS');
4189: hr_utility.set_message_token('PERSON_STATUS','placement');
4190: hr_utility.set_message_token('PERSON_TYPE','Contingent Worker');
4191: hr_utility.raise_error;
4192: else
4193: close csr_application_change_exists;
4194: end if;
4187: close csr_application_change_exists;
4188: hr_utility.set_message(800,'PER_449844_APL_ACTIONS_EXISTS');
4189: hr_utility.set_message_token('PERSON_STATUS','placement');
4190: hr_utility.set_message_token('PERSON_TYPE','Contingent Worker');
4191: hr_utility.raise_error;
4192: else
4193: close csr_application_change_exists;
4194: end if;
4195: -- end changes for bug 8405711
4204: IF csr_chk_person_is_cwk%NOTFOUND THEN
4205: --
4206: CLOSE csr_chk_person_is_cwk;
4207: --
4208: hr_utility.set_message(801,'HR_289747_MUST_BE_CWK');
4209: hr_utility.raise_error;
4210: --
4211: END IF;
4212: --
4205: --
4206: CLOSE csr_chk_person_is_cwk;
4207: --
4208: hr_utility.set_message(801,'HR_289747_MUST_BE_CWK');
4209: hr_utility.raise_error;
4210: --
4211: END IF;
4212: --
4213: CLOSE csr_chk_person_is_cwk;
4211: END IF;
4212: --
4213: CLOSE csr_chk_person_is_cwk;
4214: --
4215: hr_utility.set_location(l_proc,20);
4216: --
4217: -- Check that the CWK Employee has only ONE assignment.
4218: -- If they do not then raise an error asking the user to delete
4219: -- the person.
4224: IF csr_multi_asg_check%ROWCOUNT > 1 THEN
4225: --
4226: CLOSE csr_multi_asg_check;
4227: --
4228: hr_utility.set_message(801,'HR_289748_MULTIPLE_CWK_ASG');
4229: hr_utility.raise_error;
4230: --
4231: END IF;
4232: --
4225: --
4226: CLOSE csr_multi_asg_check;
4227: --
4228: hr_utility.set_message(801,'HR_289748_MULTIPLE_CWK_ASG');
4229: hr_utility.raise_error;
4230: --
4231: END IF;
4232: --
4233: CLOSE csr_multi_asg_check;
4231: END IF;
4232: --
4233: CLOSE csr_multi_asg_check;
4234: --
4235: hr_utility.set_location(l_proc,30);
4236: --
4237: -- Check that the CWK has no future person type changes.
4238: --
4239: IF hr_person_type_usage_info.FutSysPerTypeChgExists -- 3194314: adding person id
4240: (p_person_type_usage_id => l_person_type_usage_id
4241: ,p_effective_date => p_date_start
4242: ,p_person_id => p_person_id ) THEN
4243: -- #3684683 modified the application id to 800
4244: hr_utility.set_message(800,'HR_289749_CWK_FUTURE_PT_CHGE');
4245: hr_utility.raise_error;
4246:
4247: END IF;
4248: --
4241: ,p_effective_date => p_date_start
4242: ,p_person_id => p_person_id ) THEN
4243: -- #3684683 modified the application id to 800
4244: hr_utility.set_message(800,'HR_289749_CWK_FUTURE_PT_CHGE');
4245: hr_utility.raise_error;
4246:
4247: END IF;
4248: --
4249: hr_utility.set_location(l_proc,40);
4245: hr_utility.raise_error;
4246:
4247: END IF;
4248: --
4249: hr_utility.set_location(l_proc,40);
4250: --
4251: -- Check that the CWK has at least one previous person type.
4252: --
4253: IF NOT hr_person.chk_prev_person_type
4255: ,p_person_id => p_person_id
4256: ,p_business_group_id => p_business_group_id
4257: ,p_effective_start_date => l_effective_start_date) THEN
4258: --
4259: hr_utility.set_message(800,'HR_289750_NO_PREVIOUS_CWK');
4260: hr_utility.raise_error;
4261: --
4262: END IF;
4263: --
4256: ,p_business_group_id => p_business_group_id
4257: ,p_effective_start_date => l_effective_start_date) THEN
4258: --
4259: hr_utility.set_message(800,'HR_289750_NO_PREVIOUS_CWK');
4260: hr_utility.raise_error;
4261: --
4262: END IF;
4263: --
4264: hr_utility.set_location(l_proc,50);
4260: hr_utility.raise_error;
4261: --
4262: END IF;
4263: --
4264: hr_utility.set_location(l_proc,50);
4265: --
4266: -- Check to see if the CWK is a supervisor for another worker.
4267: -- If they are set the supervisor_warning out parameter
4268: -- to TRUE.
4282: END IF;
4283: --
4284: CLOSE csr_supervisor;
4285: --
4286: hr_utility.set_location(l_proc,60);
4287: --
4288: -- Check to see if the CWK is a recruiter for another worker.
4289: -- If they are set the recruiter_warning out parameter
4290: -- to TRUE.
4303: END IF;
4304: --
4305: CLOSE csr_recruiter;
4306: --
4307: hr_utility.set_location(l_proc,70);
4308: --
4309: -- Check to see if the CWK has any events.
4310: -- If they do set the event_warning out parameter
4311: -- to TRUE.
4324: END IF;
4325: --
4326: CLOSE csr_reviews_or_events;
4327: --
4328: hr_utility.set_location(l_proc,80);
4329: --
4330: -- Check to see if the CWK has any interviews.
4331: -- If they do set the interview_warning out parameter
4332: -- to TRUE.
4345: END IF;
4346: --
4347: CLOSE csr_interviews;
4348: --
4349: hr_utility.set_location(l_proc,90);
4350: --
4351: OPEN csr_reviews_or_events(p_type =>'I');
4352: FETCH csr_reviews_or_events into l_dummy;
4353: --
4362: END IF;
4363: --
4364: CLOSE csr_reviews_or_events;
4365: --
4366: hr_utility.set_location(l_proc,100);
4367: --
4368: -- Check to see if the CWK is the recruiter for any vacancies.
4369: -- If they do set the vacancy_warning out parameter
4370: -- to TRUE.
4383: END IF;
4384: --
4385: CLOSE csr_vacancy;
4386: --
4387: hr_utility.set_location(l_proc,110);
4388: --
4389: -- Check to see if the CWK has any requisitions.
4390: -- If they do set the requisition_warning out parameter
4391: -- to TRUE.
4404: END IF;
4405: --
4406: CLOSE csr_requisition;
4407: --
4408: hr_utility.set_location(l_proc,120);
4409: --
4410: -- Check to see if the CWK has any budget values.
4411: -- If they do set the budget_warning out parameter
4412: -- to TRUE.
4425: END IF;
4426: --
4427: CLOSE csr_budget_values;
4428: --
4429: hr_utility.set_location(l_proc,130);
4430: --
4431: OPEN csr_payment;
4432: FETCH csr_payment INTO l_dummy;
4433: --
4446: END IF;
4447: --
4448: CLOSE csr_payment;
4449: --
4450: hr_utility.set_location(l_proc,130);
4451: --
4452: EXCEPTION
4453: --
4454: WHEN hr_utility.hr_error THEN
4450: hr_utility.set_location(l_proc,130);
4451: --
4452: EXCEPTION
4453: --
4454: WHEN hr_utility.hr_error THEN
4455: raise;
4456: --
4457: WHEN OTHERS THEN
4458: --
4455: raise;
4456: --
4457: WHEN OTHERS THEN
4458: --
4459: hr_utility.oracle_error(sqlcode);
4460: hr_utility.raise_error;
4461: --
4462: END pre_cancel_placement_checks;
4463: --
4456: --
4457: WHEN OTHERS THEN
4458: --
4459: hr_utility.oracle_error(sqlcode);
4460: hr_utility.raise_error;
4461: --
4462: END pre_cancel_placement_checks;
4463: --
4464: /*===========================================================================*
4792: -- and paf.assignment_type <> 'B'; -- Added For Bug # 6630290
4793:
4794: BEGIN
4795: --
4796: hr_utility.set_location('Entering : '||l_proc,10);
4797: hr_utility.set_location(l_proc||' date_start = '||p_date_start,11);
4798: hr_utility.set_location(l_proc||' eff date = '||p_effective_date,12);
4799: --
4800: l_effective_date := TRUNC(p_effective_date);
4793:
4794: BEGIN
4795: --
4796: hr_utility.set_location('Entering : '||l_proc,10);
4797: hr_utility.set_location(l_proc||' date_start = '||p_date_start,11);
4798: hr_utility.set_location(l_proc||' eff date = '||p_effective_date,12);
4799: --
4800: l_effective_date := TRUNC(p_effective_date);
4801: l_date_start := TRUNC(p_effective_date);
4794: BEGIN
4795: --
4796: hr_utility.set_location('Entering : '||l_proc,10);
4797: hr_utility.set_location(l_proc||' date_start = '||p_date_start,11);
4798: hr_utility.set_location(l_proc||' eff date = '||p_effective_date,12);
4799: --
4800: l_effective_date := TRUNC(p_effective_date);
4801: l_date_start := TRUNC(p_effective_date);
4802: --
4799: --
4800: l_effective_date := TRUNC(p_effective_date);
4801: l_date_start := TRUNC(p_effective_date);
4802: --
4803: hr_utility.set_location(l_proc,20);
4804: --
4805: FOR supervisor_rec IN csr_supervisor LOOP
4806: --
4807: hr_utility.set_location(l_proc,60);
4803: hr_utility.set_location(l_proc,20);
4804: --
4805: FOR supervisor_rec IN csr_supervisor LOOP
4806: --
4807: hr_utility.set_location(l_proc,60);
4808: --
4809: UPDATE per_all_assignments_f paf
4810: SET paf.supervisor_id = NULL
4811: WHERE paf.rowid = supervisor_rec.rowid;
4811: WHERE paf.rowid = supervisor_rec.rowid;
4812: --
4813: IF sql%notfound THEN
4814: --
4815: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4816: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4817: hr_utility.set_message_token('STEP',1);
4818: hr_utility.raise_error;
4819: --
4812: --
4813: IF sql%notfound THEN
4814: --
4815: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4816: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4817: hr_utility.set_message_token('STEP',1);
4818: hr_utility.raise_error;
4819: --
4820: END IF;
4813: IF sql%notfound THEN
4814: --
4815: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4816: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4817: hr_utility.set_message_token('STEP',1);
4818: hr_utility.raise_error;
4819: --
4820: END IF;
4821: --
4814: --
4815: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4816: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4817: hr_utility.set_message_token('STEP',1);
4818: hr_utility.raise_error;
4819: --
4820: END IF;
4821: --
4822: END LOOP;
4820: END IF;
4821: --
4822: END LOOP;
4823: --
4824: hr_utility.set_location(l_proc,70);
4825: --
4826: FOR recruiter_rec IN csr_recruiter LOOP
4827: --
4828: hr_utility.set_location(l_proc,80);
4824: hr_utility.set_location(l_proc,70);
4825: --
4826: FOR recruiter_rec IN csr_recruiter LOOP
4827: --
4828: hr_utility.set_location(l_proc,80);
4829: --
4830: UPDATE per_all_assignments_f paf
4831: SET paf.recruiter_id = NULL
4832: WHERE paf.rowid = recruiter_rec.rowid;
4832: WHERE paf.rowid = recruiter_rec.rowid;
4833: --
4834: IF SQL%NOTFOUND THEN
4835: --
4836: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4837: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4838: hr_utility.set_message_token('STEP',2);
4839: hr_utility.raise_error;
4840: --
4833: --
4834: IF SQL%NOTFOUND THEN
4835: --
4836: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4837: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4838: hr_utility.set_message_token('STEP',2);
4839: hr_utility.raise_error;
4840: --
4841: END IF;
4834: IF SQL%NOTFOUND THEN
4835: --
4836: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4837: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4838: hr_utility.set_message_token('STEP',2);
4839: hr_utility.raise_error;
4840: --
4841: END IF;
4842: --
4835: --
4836: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4837: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4838: hr_utility.set_message_token('STEP',2);
4839: hr_utility.raise_error;
4840: --
4841: END IF;
4842: --
4843: END LOOP;
4841: END IF;
4842: --
4843: END LOOP;
4844: --
4845: hr_utility.set_location(l_proc,90);
4846: --
4847: FOR vacancies_rec IN csr_vacancies LOOP
4848: --
4849: hr_utility.set_location(l_proc,100);
4845: hr_utility.set_location(l_proc,90);
4846: --
4847: FOR vacancies_rec IN csr_vacancies LOOP
4848: --
4849: hr_utility.set_location(l_proc,100);
4850: --
4851: UPDATE per_all_vacancies pv
4852: SET pv.recruiter_id = NULL
4853: WHERE pv.rowid = vacancies_rec.rowid;
4853: WHERE pv.rowid = vacancies_rec.rowid;
4854: --
4855: IF SQL%NOTFOUND THEN
4856: --
4857: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4858: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4859: hr_utility.set_message_token('STEP',3);
4860: hr_utility.raise_error;
4861: --
4854: --
4855: IF SQL%NOTFOUND THEN
4856: --
4857: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4858: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4859: hr_utility.set_message_token('STEP',3);
4860: hr_utility.raise_error;
4861: --
4862: END IF;
4855: IF SQL%NOTFOUND THEN
4856: --
4857: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4858: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4859: hr_utility.set_message_token('STEP',3);
4860: hr_utility.raise_error;
4861: --
4862: END IF;
4863: --
4856: --
4857: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4858: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4859: hr_utility.set_message_token('STEP',3);
4860: hr_utility.raise_error;
4861: --
4862: END IF;
4863: --
4864: END LOOP;
4862: END IF;
4863: --
4864: END LOOP;
4865: --
4866: hr_utility.set_location(l_proc,110);
4867: --
4868: FOR assignment_rate_rec in csr_assignment_rate_values LOOP
4869: --
4870: hr_utility.set_location(l_proc,120);
4866: hr_utility.set_location(l_proc,110);
4867: --
4868: FOR assignment_rate_rec in csr_assignment_rate_values LOOP
4869: --
4870: hr_utility.set_location(l_proc,120);
4871: --
4872: DELETE from pay_grade_rules_f pgr
4873: WHERE pgr.rowid = assignment_rate_rec.rowid;
4874: --
4873: WHERE pgr.rowid = assignment_rate_rec.rowid;
4874: --
4875: END LOOP;
4876: --
4877: hr_utility.set_location(l_proc,130);
4878: --
4879: FOR comments_rec IN csr_comments LOOP
4880: --
4881: hr_utility.set_location(l_proc,140);
4877: hr_utility.set_location(l_proc,130);
4878: --
4879: FOR comments_rec IN csr_comments LOOP
4880: --
4881: hr_utility.set_location(l_proc,140);
4882: --
4883: DELETE FROM hr_comments h
4884: WHERE h.rowid = comments_rec.rowid;
4885: --
4884: WHERE h.rowid = comments_rec.rowid;
4885: --
4886: IF SQL%NOTFOUND THEN
4887: --
4888: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4889: hr_utility.set_message_token('PROCEDURE','cancel_placement');
4890: hr_utility.set_message_token('STEP',4);
4891: hr_utility.raise_error;
4892: --
4885: --
4886: IF SQL%NOTFOUND THEN
4887: --
4888: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4889: hr_utility.set_message_token('PROCEDURE','cancel_placement');
4890: hr_utility.set_message_token('STEP',4);
4891: hr_utility.raise_error;
4892: --
4893: END IF;
4886: IF SQL%NOTFOUND THEN
4887: --
4888: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4889: hr_utility.set_message_token('PROCEDURE','cancel_placement');
4890: hr_utility.set_message_token('STEP',4);
4891: hr_utility.raise_error;
4892: --
4893: END IF;
4894: --
4887: --
4888: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4889: hr_utility.set_message_token('PROCEDURE','cancel_placement');
4890: hr_utility.set_message_token('STEP',4);
4891: hr_utility.raise_error;
4892: --
4893: END IF;
4894: --
4895: END LOOP;
4893: END IF;
4894: --
4895: END LOOP;
4896: --
4897: hr_utility.set_location(l_proc,150);
4898: --
4899: FOR requisition_rec IN csr_requisitions LOOP
4900: --
4901: hr_utility.set_location(l_proc,160);
4897: hr_utility.set_location(l_proc,150);
4898: --
4899: FOR requisition_rec IN csr_requisitions LOOP
4900: --
4901: hr_utility.set_location(l_proc,160);
4902: --
4903: UPDATE per_requisitions pr
4904: SET pr.person_id = NULL
4905: WHERE pr.rowid = requisition_rec.rowid;
4905: WHERE pr.rowid = requisition_rec.rowid;
4906: --
4907: IF SQL%NOTFOUND THEN
4908: --
4909: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4910: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4911: hr_utility.set_message_token('STEP',5);
4912: hr_utility.raise_error;
4913: --
4906: --
4907: IF SQL%NOTFOUND THEN
4908: --
4909: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4910: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4911: hr_utility.set_message_token('STEP',5);
4912: hr_utility.raise_error;
4913: --
4914: END IF;
4907: IF SQL%NOTFOUND THEN
4908: --
4909: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4910: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4911: hr_utility.set_message_token('STEP',5);
4912: hr_utility.raise_error;
4913: --
4914: END IF;
4915: --
4908: --
4909: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4910: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4911: hr_utility.set_message_token('STEP',5);
4912: hr_utility.raise_error;
4913: --
4914: END IF;
4915: --
4916: END LOOP;
4914: END IF;
4915: --
4916: END LOOP;
4917: --
4918: hr_utility.set_location(l_proc,170);
4919: --
4920: FOR events_rec IN csr_events LOOP
4921: --
4922: hr_utility.set_location(l_proc,180);
4918: hr_utility.set_location(l_proc,170);
4919: --
4920: FOR events_rec IN csr_events LOOP
4921: --
4922: hr_utility.set_location(l_proc,180);
4923: --
4924: DELETE FROM per_bookings pb
4925: WHERE pb.rowid = events_rec.rowid;
4926: --
4925: WHERE pb.rowid = events_rec.rowid;
4926: --
4927: IF SQL%NOTFOUND THEN
4928: --
4929: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4930: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4931: hr_utility.set_message_token('STEP',6);
4932: hr_utility.raise_error;
4933: --
4926: --
4927: IF SQL%NOTFOUND THEN
4928: --
4929: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4930: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4931: hr_utility.set_message_token('STEP',6);
4932: hr_utility.raise_error;
4933: --
4934: END IF;
4927: IF SQL%NOTFOUND THEN
4928: --
4929: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4930: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4931: hr_utility.set_message_token('STEP',6);
4932: hr_utility.raise_error;
4933: --
4934: END IF;
4935: --
4928: --
4929: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4930: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4931: hr_utility.set_message_token('STEP',6);
4932: hr_utility.raise_error;
4933: --
4934: END IF;
4935: --
4936: END LOOP;
4934: END IF;
4935: --
4936: END LOOP;
4937: --
4938: hr_utility.set_location(l_proc,190);
4939: --
4940: FOR budget_rec IN csr_budget_values LOOP
4941: --
4942: hr_utility.set_location(l_proc,200);
4938: hr_utility.set_location(l_proc,190);
4939: --
4940: FOR budget_rec IN csr_budget_values LOOP
4941: --
4942: hr_utility.set_location(l_proc,200);
4943: --
4944: DELETE FROM per_assignment_budget_values_f pab
4945: WHERE pab.rowid = budget_rec.rowid;
4946: --
4945: WHERE pab.rowid = budget_rec.rowid;
4946: --
4947: IF SQL%NOTFOUND THEN
4948: --
4949: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4950: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4951: hr_utility.set_message_token('STEP',7);
4952: hr_utility.raise_error;
4953: --
4946: --
4947: IF SQL%NOTFOUND THEN
4948: --
4949: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4950: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4951: hr_utility.set_message_token('STEP',7);
4952: hr_utility.raise_error;
4953: --
4954: END IF;
4947: IF SQL%NOTFOUND THEN
4948: --
4949: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4950: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4951: hr_utility.set_message_token('STEP',7);
4952: hr_utility.raise_error;
4953: --
4954: END IF;
4955: --
4948: --
4949: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4950: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4951: hr_utility.set_message_token('STEP',7);
4952: hr_utility.raise_error;
4953: --
4954: END IF;
4955: --
4956: END LOOP budget_value;
4954: END IF;
4955: --
4956: END LOOP budget_value;
4957: --
4958: hr_utility.set_location(l_proc,210);
4959: --
4960: FOR payment_rec IN csr_payment LOOP
4961: --
4962: hr_utility.set_location(l_proc,220);
4958: hr_utility.set_location(l_proc,210);
4959: --
4960: FOR payment_rec IN csr_payment LOOP
4961: --
4962: hr_utility.set_location(l_proc,220);
4963: --
4964: DELETE FROM pay_personal_payment_methods ppm
4965: WHERE ppm.rowid = payment_rec.rowid;
4966: --
4965: WHERE ppm.rowid = payment_rec.rowid;
4966: --
4967: IF SQL%NOTFOUND THEN
4968: --
4969: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4970: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4971: hr_utility.set_message_token('STEP',8);
4972: hr_utility.raise_error;
4973: --
4966: --
4967: IF SQL%NOTFOUND THEN
4968: --
4969: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4970: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4971: hr_utility.set_message_token('STEP',8);
4972: hr_utility.raise_error;
4973: --
4974: end if;
4967: IF SQL%NOTFOUND THEN
4968: --
4969: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4970: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4971: hr_utility.set_message_token('STEP',8);
4972: hr_utility.raise_error;
4973: --
4974: end if;
4975: --
4968: --
4969: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4970: hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4971: hr_utility.set_message_token('STEP',8);
4972: hr_utility.raise_error;
4973: --
4974: end if;
4975: --
4976: END LOOP;
4974: end if;
4975: --
4976: END LOOP;
4977: --
4978: hr_utility.set_location(l_proc,230);
4979: --
4980: FOR assignment1_rec IN csr_assignments1 LOOP
4981: --
4982: hr_utility.set_location(l_proc,240);
4978: hr_utility.set_location(l_proc,230);
4979: --
4980: FOR assignment1_rec IN csr_assignments1 LOOP
4981: --
4982: hr_utility.set_location(l_proc,240);
4983: --
4984: DELETE FROM per_spinal_point_placements_f spp
4985: WHERE spp.assignment_id = assignment1_rec.assignment_id;
4986: --
4983: --
4984: DELETE FROM per_spinal_point_placements_f spp
4985: WHERE spp.assignment_id = assignment1_rec.assignment_id;
4986: --
4987: hr_utility.set_location(l_proc,250);
4988: --
4989: DELETE FROM pay_cost_allocations_f pca
4990: WHERE pca.assignment_id = assignment1_rec.assignment_id;
4991: --
4988: --
4989: DELETE FROM pay_cost_allocations_f pca
4990: WHERE pca.assignment_id = assignment1_rec.assignment_id;
4991: --
4992: hr_utility.set_location(l_proc,260);
4993: --
4994: DELETE FROM per_all_assignments_f paf
4995: WHERE paf.rowid = assignment1_rec.rowid;
4996: --
4995: WHERE paf.rowid = assignment1_rec.rowid;
4996: --
4997: IF SQL%NOTFOUND THEN
4998: --
4999: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5000: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5001: hr_utility.set_message_token('STEP',9);
5002: hr_utility.raise_error;
5003: --
4996: --
4997: IF SQL%NOTFOUND THEN
4998: --
4999: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5000: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5001: hr_utility.set_message_token('STEP',9);
5002: hr_utility.raise_error;
5003: --
5004: END IF;
4997: IF SQL%NOTFOUND THEN
4998: --
4999: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5000: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5001: hr_utility.set_message_token('STEP',9);
5002: hr_utility.raise_error;
5003: --
5004: END IF;
5005: --
4998: --
4999: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5000: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5001: hr_utility.set_message_token('STEP',9);
5002: hr_utility.raise_error;
5003: --
5004: END IF;
5005: --
5006: END LOOP;
5004: END IF;
5005: --
5006: END LOOP;
5007: --
5008: hr_utility.set_location(l_proc,270);
5009: --
5010: FOR applicantions_rec IN csr_applications LOOP
5011: --
5012: hr_utility.set_location(l_proc,280);
5008: hr_utility.set_location(l_proc,270);
5009: --
5010: FOR applicantions_rec IN csr_applications LOOP
5011: --
5012: hr_utility.set_location(l_proc,280);
5013: --
5014: --
5015: -- Changes start for the bug 13558312
5016: open csr_is_apl_ex_apl;
5020:
5021: --if l_ex_apl_person_type_id is not null then -- Commented for the bug#13576986
5022: if l_cur_sys_person_type is not null and l_cur_sys_person_type = 'EX_APL' then -- Added for the bug#13576986
5023: --
5024: hr_utility.set_location(l_proc,444);
5025: --
5026: else
5027: hr_utility.set_location(l_proc,445);
5028:
5023: --
5024: hr_utility.set_location(l_proc,444);
5025: --
5026: else
5027: hr_utility.set_location(l_proc,445);
5028:
5029: UPDATE per_applications pap
5030: SET pap.date_end = NULL
5031: WHERE pap.rowid = applicantions_rec.rowid;
5039:
5040:
5041: IF SQL%NOTFOUND THEN
5042: --
5043: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5044: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5045: hr_utility.set_message_token('STEP',10);
5046: hr_utility.raise_error;
5047: --
5040:
5041: IF SQL%NOTFOUND THEN
5042: --
5043: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5044: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5045: hr_utility.set_message_token('STEP',10);
5046: hr_utility.raise_error;
5047: --
5048: END IF;
5041: IF SQL%NOTFOUND THEN
5042: --
5043: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5044: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5045: hr_utility.set_message_token('STEP',10);
5046: hr_utility.raise_error;
5047: --
5048: END IF;
5049: --
5042: --
5043: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5044: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5045: hr_utility.set_message_token('STEP',10);
5046: hr_utility.raise_error;
5047: --
5048: END IF;
5049: --
5050: END LOOP;
5048: END IF;
5049: --
5050: END LOOP;
5051: --
5052: hr_utility.set_location(l_proc,320);
5053: --
5054: -- Change for the bug 6460093 starts here
5055: OPEN csr_pdped_start;
5056: FETCH csr_pdped_start into c_effective_start_date;
5060: OPEN csr_periods_of_placement;
5061: --
5062: LOOP
5063: --
5064: hr_utility.set_location(l_proc,330);
5065: --
5066: FETCH csr_periods_of_placement INTO l_rowid;
5067: --
5068: EXIT WHEN csr_periods_of_placement%NOTFOUND;
5073: IF SQL%NOTFOUND THEN
5074: --
5075: CLOSE csr_periods_of_placement;
5076: --
5077: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5078: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5079: hr_utility.set_message_token('STEP',11);
5080: hr_utility.raise_error;
5081: --
5074: --
5075: CLOSE csr_periods_of_placement;
5076: --
5077: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5078: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5079: hr_utility.set_message_token('STEP',11);
5080: hr_utility.raise_error;
5081: --
5082: END IF;
5075: CLOSE csr_periods_of_placement;
5076: --
5077: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5078: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5079: hr_utility.set_message_token('STEP',11);
5080: hr_utility.raise_error;
5081: --
5082: END IF;
5083: --
5076: --
5077: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5078: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5079: hr_utility.set_message_token('STEP',11);
5080: hr_utility.raise_error;
5081: --
5082: END IF;
5083: --
5084: END LOOP;
5082: END IF;
5083: --
5084: END LOOP;
5085: --
5086: hr_utility.set_location(l_proc,340);
5087: --
5088: IF csr_periods_of_placement%ROWCOUNT <1 THEN
5089: --
5090: CLOSE csr_periods_of_placement;
5088: IF csr_periods_of_placement%ROWCOUNT <1 THEN
5089: --
5090: CLOSE csr_periods_of_placement;
5091: --
5092: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5093: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5094: hr_utility.set_message_token('STEP',12);
5095: hr_utility.raise_error;
5096: --
5089: --
5090: CLOSE csr_periods_of_placement;
5091: --
5092: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5093: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5094: hr_utility.set_message_token('STEP',12);
5095: hr_utility.raise_error;
5096: --
5097: END IF;
5090: CLOSE csr_periods_of_placement;
5091: --
5092: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5093: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5094: hr_utility.set_message_token('STEP',12);
5095: hr_utility.raise_error;
5096: --
5097: END IF;
5098: --
5091: --
5092: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5093: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5094: hr_utility.set_message_token('STEP',12);
5095: hr_utility.raise_error;
5096: --
5097: END IF;
5098: --
5099: CLOSE csr_periods_of_placement;
5097: END IF;
5098: --
5099: CLOSE csr_periods_of_placement;
5100: --
5101: hr_utility.set_location(l_proc,350);
5102: --
5103: -- Check that there is not a previous period of placement
5104: -- record that ended the day before the current period of placement
5105: -- record. I.e is a back to back hire.
5108: FETCH csr_pop_back_to_back INTO l_dummy_id;
5109: --
5110: IF csr_pop_back_to_back%FOUND THEN
5111: --
5112: hr_utility.set_location(l_proc,353);
5113: --
5114: l_pop_back_to_back := TRUE;
5115: --
5116: ELSE
5119: -- check that there is not a record for period of service
5120: -- that ends the day before the period of placement record
5121: -- being canceled.
5122: --
5123: hr_utility.set_location(l_proc,355);
5124: --
5125: l_pop_back_to_back := FALSE;
5126: --
5127: OPEN csr_pos_back_to_back (p_date_start => p_date_start);
5128: FETCH csr_pos_back_to_back INTO l_dummy_id;
5129: --
5130: IF csr_pos_back_to_back%FOUND THEN
5131: --
5132: hr_utility.set_location(l_proc,356);
5133: --
5134: l_pos_back_to_back := TRUE;
5135: --
5136: ELSE
5134: l_pos_back_to_back := TRUE;
5135: --
5136: ELSE
5137: --
5138: hr_utility.set_location(l_proc,357);
5139: --
5140: l_pos_back_to_back := FALSE;
5141: --
5142: END IF;
5149: --
5150: if NOT l_pop_back_to_back then -- 3194314
5151: FOR assignment2_rec IN csr_assignments2 LOOP
5152: --
5153: hr_utility.set_location(l_proc,300);
5154: --
5155: UPDATE per_all_assignments_f paf
5156: SET paf.effective_end_date = l_end_of_time
5157: WHERE paf.rowid = assignment2_rec.rowid;
5155: UPDATE per_all_assignments_f paf
5156: SET paf.effective_end_date = l_end_of_time
5157: WHERE paf.rowid = assignment2_rec.rowid;
5158: --
5159: hr_utility.set_location(l_proc,310);
5160: --
5161: END LOOP;
5162: end if;
5163: --
5160: --
5161: END LOOP;
5162: end if;
5163: --
5164: hr_utility.set_location(l_proc,360);
5165: --
5166: FOR person_rec IN csr_person LOOP
5167: --
5168: l_person_rec_found := TRUE;
5166: FOR person_rec IN csr_person LOOP
5167: --
5168: l_person_rec_found := TRUE;
5169: --
5170: hr_utility.set_location(l_proc,370);
5171: --
5172: IF l_pop_back_to_back THEN
5173: --
5174: hr_utility.set_location(l_proc,380);
5170: hr_utility.set_location(l_proc,370);
5171: --
5172: IF l_pop_back_to_back THEN
5173: --
5174: hr_utility.set_location(l_proc,380);
5175: --
5176: IF person_rec.effective_end_date = hr_general.end_of_time THEN
5177: --
5178: hr_utility.set_location(l_proc,390);
5174: hr_utility.set_location(l_proc,380);
5175: --
5176: IF person_rec.effective_end_date = hr_general.end_of_time THEN
5177: --
5178: hr_utility.set_location(l_proc,390);
5179: --
5180: OPEN csr_get_person_type (p_system_person_type => 'EX_CWK');
5181: FETCH csr_get_person_type INTO l_person_type_id;
5182: --
5202: WHERE rowid = person_rec.rowid;
5203: --
5204: ELSE -- #1998140
5205: --
5206: hr_utility.set_location(l_proc,400);
5207: --
5208: DELETE FROM per_people_f
5209: WHERE rowid = person_rec.rowid;
5210: --
5216:
5217: IF person_rec.effective_end_date = hr_general.end_of_time THEN
5218:
5219: -- it should restore the EX_EMP record instead of removing it
5220: hr_utility.set_location(l_proc,405);
5221: --
5222: --
5223: -- Changes start for the bug 13772471
5224:
5237:
5238: --
5239: ELSE -- #1998140
5240: --
5241: hr_utility.set_location(l_proc,406);
5242: --
5243: DELETE FROM per_people_f
5244: WHERE rowid = person_rec.rowid;
5245: --
5248: -- << 3194314
5249:
5250: ELSE
5251: --
5252: hr_utility.set_location(l_proc,410);
5253: --
5254: --
5255: -- Changes start for the bug 13442055
5256:
5262: --if l_ex_apl_person_type_id is not null then -- Commented for the bug#13576986
5263: if l_cur_sys_person_type is not null and -- Added for the bug#13576986
5264: (l_cur_sys_person_type = 'EX_APL' or l_cur_sys_person_type = 'APL') then
5265: --
5266: hr_utility.set_location(l_proc,416);
5267: --
5268: --
5269: -- Changes start for the bug 13772471
5270:
5280: current_npw_flag = null
5281: WHERE rowid = person_rec.rowid;
5282: else
5283: --
5284: hr_utility.set_location(l_proc,420); -- Moved this debug line here to resolve Bug#13964489
5285: --
5286: DELETE FROM per_people_f
5287: WHERE rowid = person_rec.rowid;
5288: --
5296: --
5297:
5298: IF SQL%NOTFOUND THEN
5299: --
5300: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5301: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5302: hr_utility.set_message_token('STEP',13);
5303: hr_utility.raise_error;
5304: --
5297:
5298: IF SQL%NOTFOUND THEN
5299: --
5300: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5301: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5302: hr_utility.set_message_token('STEP',13);
5303: hr_utility.raise_error;
5304: --
5305: END IF;
5298: IF SQL%NOTFOUND THEN
5299: --
5300: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5301: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5302: hr_utility.set_message_token('STEP',13);
5303: hr_utility.raise_error;
5304: --
5305: END IF;
5306: --
5299: --
5300: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5301: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5302: hr_utility.set_message_token('STEP',13);
5303: hr_utility.raise_error;
5304: --
5305: END IF;
5306: --
5307: END LOOP;
5305: END IF;
5306: --
5307: END LOOP;
5308: --
5309: -- hr_utility.set_location(l_proc,430); -- for bug 14530580
5310: --
5311: IF NOT l_person_rec_found THEN
5312: --
5313: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5309: -- hr_utility.set_location(l_proc,430); -- for bug 14530580
5310: --
5311: IF NOT l_person_rec_found THEN
5312: --
5313: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5314: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5315: hr_utility.set_message_token('STEP',14);
5316: hr_utility.raise_error;
5317: --
5310: --
5311: IF NOT l_person_rec_found THEN
5312: --
5313: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5314: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5315: hr_utility.set_message_token('STEP',14);
5316: hr_utility.raise_error;
5317: --
5318: END IF;
5311: IF NOT l_person_rec_found THEN
5312: --
5313: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5314: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5315: hr_utility.set_message_token('STEP',14);
5316: hr_utility.raise_error;
5317: --
5318: END IF;
5319: --
5312: --
5313: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5314: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5315: hr_utility.set_message_token('STEP',14);
5316: hr_utility.raise_error;
5317: --
5318: END IF;
5319: --
5320: -- hr_utility.set_location(l_proc,440); -- for bug 14530580
5316: hr_utility.raise_error;
5317: --
5318: END IF;
5319: --
5320: -- hr_utility.set_location(l_proc,440); -- for bug 14530580
5321: --
5322: if NOT l_pos_back_to_back then -- 3194314 this should not get executed if b2b emp/cwk
5323:
5324: FOR new_person_rec IN csr_new_person LOOP
5323:
5324: FOR new_person_rec IN csr_new_person LOOP
5325:
5326: --
5327: -- hr_utility.set_location(l_proc,450); -- for bug 14530580
5328: --
5329: l_new_person_found := TRUE;
5330: --
5331: IF NOT l_pop_back_to_back THEN
5329: l_new_person_found := TRUE;
5330: --
5331: IF NOT l_pop_back_to_back THEN
5332: --
5333: -- hr_utility.set_location(l_proc,460); -- for bug 14530580
5334: --
5335: --
5336: -- Changes start for the bug 13558312
5337: open csr_is_apl_ex_apl;
5343: if l_cur_sys_person_type is not null and -- Added for the bug#13576986
5344: (l_cur_sys_person_type = 'EX_APL' or l_cur_sys_person_type = 'APL') then
5345: --
5346: -- modified for bug 14530580
5347: -- hr_utility.set_location(l_proc,555);
5348: NULL;
5349: -- modified for bug 14530580
5350: --
5351: else
5348: NULL;
5349: -- modified for bug 14530580
5350: --
5351: else
5352: hr_utility.set_location(l_proc,556);
5353:
5354: UPDATE per_people_f
5355: SET effective_end_date = l_end_of_time
5356: WHERE rowid = new_person_rec.rowid;
5365:
5366:
5367: IF sql%ROWCOUNT <1 then
5368: --
5369: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5370: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5371: hr_utility.set_message_token('STEP',15);
5372: hr_utility.raise_error;
5373: --
5366:
5367: IF sql%ROWCOUNT <1 then
5368: --
5369: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5370: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5371: hr_utility.set_message_token('STEP',15);
5372: hr_utility.raise_error;
5373: --
5374: END IF;
5367: IF sql%ROWCOUNT <1 then
5368: --
5369: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5370: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5371: hr_utility.set_message_token('STEP',15);
5372: hr_utility.raise_error;
5373: --
5374: END IF;
5375: --
5368: --
5369: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5370: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5371: hr_utility.set_message_token('STEP',15);
5372: hr_utility.raise_error;
5373: --
5374: END IF;
5375: --
5376: END LOOP;
5376: END LOOP;
5377: end if; -- << 3194314
5378:
5379: --
5380: hr_utility.set_location(l_proc,480);
5381: --
5382: IF NOT l_new_person_found THEN
5383: --
5384: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5380: hr_utility.set_location(l_proc,480);
5381: --
5382: IF NOT l_new_person_found THEN
5383: --
5384: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5385: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5386: hr_utility.set_message_token('STEP',16);
5387: hr_utility.raise_error;
5388: --
5381: --
5382: IF NOT l_new_person_found THEN
5383: --
5384: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5385: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5386: hr_utility.set_message_token('STEP',16);
5387: hr_utility.raise_error;
5388: --
5389: END IF;
5382: IF NOT l_new_person_found THEN
5383: --
5384: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5385: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5386: hr_utility.set_message_token('STEP',16);
5387: hr_utility.raise_error;
5388: --
5389: END IF;
5390: --
5383: --
5384: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5385: hr_utility.set_message_token('PROCEDURE','cancel_placement');
5386: hr_utility.set_message_token('STEP',16);
5387: hr_utility.raise_error;
5388: --
5389: END IF;
5390: --
5391: -- If there are no back to back records for
5394: --
5395: IF NOT l_pop_back_to_back AND
5396: NOT l_pos_back_to_back THEN
5397: --
5398: hr_utility.set_location(l_proc,490);
5399: --
5400: --
5401: --
5402: -- Changes start for the bug 13442055
5413: l_object_version_number;
5414: --
5415: IF csr_get_ptu_details%FOUND THEN
5416: --
5417: hr_utility.set_location(l_proc,503);
5418: --
5419: hr_per_type_usage_internal.delete_person_type_usage
5420: (p_validate => FALSE
5421: ,p_person_type_usage_id => l_person_type_usage_id
5456: -- bug fix 6992346
5457: -- If there are back to back records for period of service
5458: -- and period of placements then
5459: elsif NOT l_pop_back_to_back AND l_pos_back_to_back THEN
5460: hr_utility.set_location(l_proc,491);
5461: hr_utility.set_location('l_date_start '||l_date_start,491);
5462: --
5463: --
5464: -- Changes start for the bug 13442055
5457: -- If there are back to back records for period of service
5458: -- and period of placements then
5459: elsif NOT l_pop_back_to_back AND l_pos_back_to_back THEN
5460: hr_utility.set_location(l_proc,491);
5461: hr_utility.set_location('l_date_start '||l_date_start,491);
5462: --
5463: --
5464: -- Changes start for the bug 13442055
5465: open csr_is_apl_ex_apl;
5474: l_object_version_number;
5475: --
5476: IF csr_get_ptu_details%FOUND THEN
5477: --
5478: hr_utility.set_location(l_proc,504);
5479: --
5480: hr_per_type_usage_internal.delete_person_type_usage
5481: (p_validate => FALSE
5482: ,p_person_type_usage_id => l_person_type_usage_id
5500:
5501: ELSIF l_pos_back_to_back and l_pop_back_to_back THEN
5502: --ELSIF l_pos_back_to_back THEN
5503: --
5504: hr_utility.set_location(l_proc,500);
5505: --
5506: OPEN csr_get_ptu_details;
5507: FETCH csr_get_ptu_details INTO l_person_type_usage_id,
5508: l_object_version_number;
5508: l_object_version_number;
5509: --
5510: IF csr_get_ptu_details%FOUND THEN
5511: --
5512: hr_utility.set_location(l_proc,510);
5513: --
5514: hr_per_type_usage_internal.delete_person_type_usage
5515: (p_validate => FALSE
5516: ,p_person_type_usage_id => l_person_type_usage_id
5528: per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);
5529: --
5530: for asg_sec_rec in csr_asg_sec loop
5531: --
5532: hr_utility.set_location(l_proc,300);
5533: -- do some security maintenance.
5534: -- reset the security access(per_person_list) for this assignment
5535: hr_security_internal.add_to_person_list(
5536: p_effective_date => asg_sec_rec.effective_start_date
5537: ,p_assignment_id => asg_sec_rec.assignment_id);
5538: --
5539: end loop;
5540: --
5541: hr_utility.set_location('Leaving : '||l_proc,999);
5542: --
5543: END do_cancel_placement;
5544: --
5545: -- ---------------------------------------------------------------------------
5564: l_proc VARCHAR2(72) := g_package||'return_legislation_code';
5565: --
5566: BEGIN
5567: --
5568: hr_utility.set_location('Entering:'|| l_proc, 10);
5569: --
5570: -- Ensure that all the mandatory parameter are not null
5571: --
5572: hr_api.mandatory_arg_error
5587: fnd_message.raise_error;
5588: --
5589: END IF;
5590: --
5591: hr_utility.set_location(l_proc,20);
5592: --
5593: CLOSE csr_leg_code;
5594: --
5595: hr_utility.set_location(' Leaving:'|| l_proc, 999);
5591: hr_utility.set_location(l_proc,20);
5592: --
5593: CLOSE csr_leg_code;
5594: --
5595: hr_utility.set_location(' Leaving:'|| l_proc, 999);
5596: --
5597: RETURN l_legislation_code;
5598: --
5599: END return_legislation_code;