[Home] [Help]
107: SAVEPOINT ASSIGN_RESOURCE_INV_LOC_PUB;
108:
109: -- initialize message list
110: IF fnd_api.to_boolean(p_init_msg_list) THEN
111: FND_MSG_PUB.initialize;
112: END IF;
113:
114: -- Standard call to check for call compatibility.
115: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
133: -- start full validations
134: IF p_organization_id IS NULL THEN
135: FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
136: FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
137: FND_MSG_PUB.ADD;
138: RAISE EXCP_USER_DEFINED;
139: ELSIF p_organization_id IS NOT NULL AND p_action_code IN (0,1) THEN
140: -- check whether the organizaton exists.
141: BEGIN
144: where organization_id = p_organization_id;
145: EXCEPTION
146: WHEN NO_DATA_FOUND THEN
147: FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
148: FND_MSG_PUB.ADD;
149: RAISE EXCP_USER_DEFINED;
150: WHEN OTHERS THEN
151: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
152: fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
151: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
152: fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
153: fnd_message.set_token('ROUTINE', l_api_name, TRUE);
154: fnd_message.set_token('TABLE', 'mtl_organizations', TRUE);
155: FND_MSG_PUB.ADD;
156: RAISE EXCP_USER_DEFINED;
157: END;
158: ELSE -- it must be p_organization_id = null and action_code = 2. do nothing for this case.
159: NULL;
161:
162: IF p_subinventory_code IS NULL THEN
163: FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
164: FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_subinventory_code', TRUE);
165: FND_MSG_PUB.ADD;
166: RAISE EXCP_USER_DEFINED;
167: -- check whether the subinventory is a Spare subinventory in the organization.
168: ELSE
169: /* BEGIN
174:
175: EXCEPTION
176: WHEN NO_DATA_FOUND THEN
177: fnd_message.set_name('CSP', 'CSP_NOT_SPARES_SUB');
178: fnd_msg_pub.add;
179: RAISE EXCP_USER_DEFINED;
180: WHEN TOO_MANY_ROWS THEN
181: -- this is a valid situation
182: NULL;
184: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
185: fnd_message.set_token('ERR_FIELD', 'p_subinventory_code', TRUE);
186: fnd_message.set_token('ROUTINE', l_api_name, TRUE);
187: fnd_message.set_token('TABLE', 'csp_sec_inventories', TRUE);
188: FND_MSG_PUB.ADD;
189: RAISE EXCP_USER_DEFINED;
190: END;
191: */
192: -- valide the p_locator_id if it is not null
200:
201: EXCEPTION
202: WHEN NO_DATA_FOUND THEN
203: fnd_message.set_name('INV', 'INV_LOCATOR_NOT_AVAILABLE');
204: fnd_msg_pub.add;
205: RAISE EXCP_USER_DEFINED;
206: /*WHEN TOO_MANY_ROWS THEN
207: l_msg_data := 'More than one same Locator ID was found. The locator table might not be set up correctly.';
208: RAISE EXCP_USER_DEFINED;*/
210: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
211: fnd_message.set_token('ERR_FIELD', 'p_locator_id', TRUE);
212: fnd_message.set_token('ROUTINE', l_api_name, TRUE);
213: fnd_message.set_token('TABLE', 'mtl_item_locations', TRUE);
214: fnd_msg_pub.ADD;
215: --l_msg_data := 'Unexpected errors occurred while validating the Locator ID. Please contact your system administrator.';
216: RAISE EXCP_USER_DEFINED;
217: END;
218: END IF;
220:
221: IF p_resource_id IS NULL THEN
222: FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
223: FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_resource_id', TRUE);
224: FND_MSG_PUB.ADD;
225: RAISE EXCP_USER_DEFINED;
226:
227: -- check whether the resource id and resource type exist.
228: ELSE
236:
237: EXCEPTION
238: WHEN NO_DATA_FOUND THEN
239: fnd_message.set_name('CSP', 'CSP_INVALID_RES_ID_TYPE');
240: fnd_msg_pub.add;
241: RAISE EXCP_USER_DEFINED;
242: /*WHEN TOO_MANY_ROWS THEN
243: l_msg_data := 'Duplicate Resource ID found. There may be an error in your Resource table.';
244: RAISE EXCP_USER_DEFINED;*/
246: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
247: fnd_message.set_token('ERR_FIELD', 'p_resource_id', TRUE);
248: fnd_message.set_token('ROUTINE', l_api_name, TRUE);
249: fnd_message.set_token('TABLE', 'jtf_rs_all_resources_vl', TRUE);
250: fnd_msg_pub.ADD;
251: RAISE EXCP_USER_DEFINED;
252: END;
253: END IF;
254:
291: IF upper(l_default_code) = 'IN' THEN
292: IF fnd_api.to_boolean(l_bad_type) AND NOT fnd_api.to_boolean(l_good_type) THEN
293:
294: fnd_message.set_name('CSP', 'CSP_INVALID_IN_OUT_SUB');
295: fnd_msg_pub.ADD;
296: RAISE EXCP_USER_DEFINED;
297: END IF;
298:
299: ELSIF upper(l_default_code) = 'OUT' THEN
299: ELSIF upper(l_default_code) = 'OUT' THEN
300: IF fnd_api.to_boolean(l_good_type) AND NOT fnd_api.to_boolean(l_bad_type)THEN
301:
302: fnd_message.set_name('CSP', 'CSP_INVALID_IN_OUT_SUB');
303: fnd_msg_pub.ADD;
304: RAISE EXCP_USER_DEFINED;
305: END IF;
306:
307: ELSIF l_default_code IS NOT NULL AND NOT fnd_api.to_boolean(l_good_type)
307: ELSIF l_default_code IS NOT NULL AND NOT fnd_api.to_boolean(l_good_type)
308: AND NOT fnd_api.to_boolean(l_bad_type) AND p_action_code = 0 THEN
309:
310: fnd_message.set_name('CSP', 'CSP_RES_INV_WARNING');
311: fnd_msg_pub.ADD;
312: l_default_code := NULL;
313: ELSE NULL;
314: END IF;
315:
331: -- It's a good case.
332: NULL;
333: WHEN TOO_MANY_ROWS THEN
334: fnd_message.set_name('CSP', 'CSP_DUPLICATE_RECORD');
335: fnd_msg_pub.ADD;
336: RAISE EXCP_USER_DEFINED;
337: WHEN OTHERS THEN
338: --l_msg_data := SQLERRM(SQLCODE);
339: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
339: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
340: fnd_message.set_token('ERR_FIELD', 'px_csp_inv_loc_assignment_id', TRUE);
341: fnd_message.set_token('ROUTINE', l_api_name, TRUE);
342: fnd_message.set_token('TABLE', 'csp_inv_loc_assignments', TRUE);
343: fnd_msg_pub.ADD;
344: RAISE EXCP_USER_DEFINED;
345: END;
346: END IF;
347:
346: END IF;
347:
348: /* IF p_EFFECTIVE_DATE_START IS NULL OR p_EFFECTIVE_DATE_END IS NULL THEN
349: fnd_message.set_name('CSP', 'CSP_INVALID_START_END_DATES');
350: fnd_msg_pub.add;
351: RAISE EXCP_USER_DEFINED;
352: END IF;
353: */
354: -- validate whether the combination already exists, if yes, raise the EXCP_DUPLICATE_RECORD exception
361: ,x_return_status => l_record_status);
362:
363: IF FND_API.to_Boolean(l_record_status ) THEN
364: fnd_message.set_name('CSP', 'CSP_DUPLICATE_RECORD');
365: fnd_msg_pub.ADD;
366: RAISE EXCP_USER_DEFINED;
367: END IF;
368:
369: -- construct the l_csp_rec record
414: -- make sure the record exists.
415: IF px_CSP_INV_LOC_ASSIGNMENT_ID IS NULL THEN
416: FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
417: FND_MESSAGE.SET_TOKEN ('PARAMETER', 'px_csp_inv_loc_assignment_id', TRUE);
418: FND_MSG_PUB.ADD;
419: RAISE EXCP_USER_DEFINED;
420: ELSE
421: BEGIN
422: SELECT csp_inv_loc_assignment_id INTO l_check_existence
427: EXCEPTION
428: WHEN NO_DATA_FOUND THEN
429: fnd_message.set_name('CSP', 'CSP_INVALID_ASSIGNMENT_ID');
430: fnd_message.set_token('ASSIGNMENT_ID', to_char(px_csp_inv_loc_assignment_id), TRUE);
431: fnd_msg_pub.add;
432: RAISE EXCP_USER_DEFINED;
433: -- WHEN TOO_MANY_ROWS THEN
434: -- l_msg_data := 'Too many Assignment ID '||px_csp_inv_loc_assignment_id||' found. You may have a data setup problem.';
435: -- RAISE EXCP_USER_DEFINED;
439: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
440: fnd_message.set_token('ERR_FIELD', 'px_csp_inv_loc_assignment_id', TRUE);
441: fnd_message.set_token('ROUTINE', l_api_name, TRUE);
442: fnd_message.set_token('TABLE', 'csp_inv_loc_assignments', TRUE);
443: fnd_msg_pub.ADD;
444: RAISE EXCP_USER_DEFINED;
445: END;
446: -- construct the l_csp_rec record
447: l_csp_rec.CSP_INV_LOC_ASSIGNMENT_ID := px_CSP_INV_LOC_ASSIGNMENT_ID;
522: IF C_Get_Inv_Loc_Assignments%NOTFOUND THEN
523: CLOSE C_Get_Inv_Loc_Assignments;
524: fnd_message.set_name('CSP', 'CSP_INVALID_ASSIGNMENT_ID');
525: fnd_message.set_token('ASSIGNMENT_ID', to_char(px_csp_inv_loc_assignment_id), TRUE);
526: fnd_msg_pub.add;
527: RAISE EXCP_USER_DEFINED;
528: END IF;
529: CLOSE C_Get_Inv_Loc_Assignments;
530:
601: -- AND NOT fnd_api.to_boolean(l_bad_type) THEN
602: -- l_msg_data := 'Warning: Default Code is not an "IN" or an "OUT" type. Please query the record and re-assign a valid Default Code.';
603: -- fnd_message.set_name(l_msg_data, 'API_ASSIGN_RESOURCE_INV_LOC');
604: -- fnd_message.set_token('ROUTINE', 'Assign_Resource_Inv_Loc');
605: -- fnd_msg_pub.ADD;
606: -- l_default_code := NULL;
607: ELSE NULL;
608: END IF;
609: END;
632: */
633: NULL;
634: WHEN TOO_MANY_ROWS THEN
635: fnd_message.set_name('CSP', 'CSP_DUPLICATE_RECORD');
636: fnd_msg_pub.add;
637: RAISE EXCP_USER_DEFINED;
638: WHEN EXCP_USER_DEFINED THEN
639: RAISE EXCP_USER_DEFINED;
640: WHEN OTHERS THEN
643: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
644: fnd_message.set_token('ERR_FIELD', 'px_csp_inv_loc_assignment_id', TRUE);
645: fnd_message.set_token('ROUTINE', l_api_name, TRUE);
646: fnd_message.set_token('TABLE', 'csp_inv_loc_assignments', TRUE);
647: fnd_msg_pub.ADD;
648: RAISE EXCP_USER_DEFINED;
649: END;
650:
651: -- call the update procedure
686: ELSE
687: -- l_msg_data := 'p_action_code must be 0, 1, or 2.';
688: fnd_message.set_name('INV', 'INV-INVALID ACTION');
689: fnd_message.set_token('ROUTINE', l_api_name, TRUE);
690: fnd_msg_pub.add;
691: RAISE EXCP_USER_DEFINED;
692: END IF;
693:
694: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
703:
704: EXCEPTION
705: WHEN EXCP_USER_DEFINED THEN
706:
707: fnd_msg_pub.count_and_get
708: ( p_count => x_msg_count
709: , p_data => x_msg_data);
710: x_return_status := FND_API.G_RET_STS_ERROR;
711:
713:
714: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
715: P_API_NAME => L_API_NAME
716: ,P_PKG_NAME => G_PKG_NAME
717: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
718: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
719: ,X_MSG_COUNT => X_MSG_COUNT
720: ,X_MSG_DATA => X_MSG_DATA
721: ,X_RETURN_STATUS => X_RETURN_STATUS);
724:
725: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
726: P_API_NAME => L_API_NAME
727: ,P_PKG_NAME => G_PKG_NAME
728: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
729: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
730: ,X_MSG_COUNT => X_MSG_COUNT
731: ,X_MSG_DATA => X_MSG_DATA
732: ,X_RETURN_STATUS => X_RETURN_STATUS);