[Home] [Help]
156: -- OverLoaded Procedure insertSerial for eAM
157: PROCEDURE insertserial
158: (
159: p_api_version IN NUMBER
160: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
161: , p_commit IN VARCHAR2 := fnd_api.g_false
162: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
163: , p_inventory_item_id IN NUMBER
164: , p_organization_id IN NUMBER
157: PROCEDURE insertserial
158: (
159: p_api_version IN NUMBER
160: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
161: , p_commit IN VARCHAR2 := fnd_api.g_false
162: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
163: , p_inventory_item_id IN NUMBER
164: , p_organization_id IN NUMBER
165: , p_serial_number IN VARCHAR2
158: (
159: p_api_version IN NUMBER
160: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
161: , p_commit IN VARCHAR2 := fnd_api.g_false
162: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
163: , p_inventory_item_id IN NUMBER
164: , p_organization_id IN NUMBER
165: , p_serial_number IN VARCHAR2
166: , p_current_status IN NUMBER
194: -- Standard Start of API savepoint
195: SAVEPOINT apiinsertserial_apipub;
196:
197: -- Standard call to check for call compatibility.
198: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
199: RAISE fnd_api.g_exc_unexpected_error;
200: END IF;
201:
202: -- Initialize message list if p_init_msg_list is set to TRUE.
195: SAVEPOINT apiinsertserial_apipub;
196:
197: -- Standard call to check for call compatibility.
198: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
199: RAISE fnd_api.g_exc_unexpected_error;
200: END IF;
201:
202: -- Initialize message list if p_init_msg_list is set to TRUE.
203: IF fnd_api.to_boolean(p_init_msg_list) THEN
199: RAISE fnd_api.g_exc_unexpected_error;
200: END IF;
201:
202: -- Initialize message list if p_init_msg_list is set to TRUE.
203: IF fnd_api.to_boolean(p_init_msg_list) THEN
204: fnd_msg_pub.initialize;
205: END IF;
206:
207: -- Initialize API return status to success
221: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
222: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
223: END IF;
224:
225: RAISE fnd_api.g_exc_unexpected_error;
226: END;
227:
228: IF (item_count = 0) THEN
229: fnd_message.set_name('INV', 'INV_INVALID_ORGANIZATION');
228: IF (item_count = 0) THEN
229: fnd_message.set_name('INV', 'INV_INVALID_ORGANIZATION');
230: fnd_msg_pub.ADD;
231: --Bug 3153585:Raising exception to populate error message correctly.
232: RAISE fnd_api.g_exc_error;
233: END IF;
234:
235: -- Block to check the Serial Control Code
236: BEGIN
246: AND is_serial_tagged(p_inventory_item_id,p_organization_id,NULL) = 1) THEN
247: fnd_message.set_name('INV', 'INV_ITEM_NOT_SERIAL_CONTROLLED');
248: fnd_msg_pub.ADD;
249: --Bug 3153585:Raising exception to populate error message correctly.
250: RAISE fnd_api.g_exc_error;
251: END IF;
252:
253: IF eam_item IS NULL THEN
254: l_current_status := 1;
255: ELSE
256: l_current_status := p_current_status;
257: END IF;
258: EXCEPTION
259: WHEN fnd_api.g_exc_error THEN
260: RAISE fnd_api.g_exc_error;
261: --Bug 3153585:Raising exception to populate error message correctly.
262: WHEN NO_DATA_FOUND THEN
263: fnd_message.set_name('INV', 'INV_INVALID_ITEM');
256: l_current_status := p_current_status;
257: END IF;
258: EXCEPTION
259: WHEN fnd_api.g_exc_error THEN
260: RAISE fnd_api.g_exc_error;
261: --Bug 3153585:Raising exception to populate error message correctly.
262: WHEN NO_DATA_FOUND THEN
263: fnd_message.set_name('INV', 'INV_INVALID_ITEM');
264: fnd_msg_pub.ADD;
262: WHEN NO_DATA_FOUND THEN
263: fnd_message.set_name('INV', 'INV_INVALID_ITEM');
264: fnd_msg_pub.ADD;
265: --Bug 3153585:Raising exception to populate error message correctly.
266: RAISE fnd_api.g_exc_error;
267: WHEN OTHERS THEN
268: --Bug 3152585:Raising Exception to populate error message correctly.
269: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
270: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
269: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
270: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
271: END IF;
272:
273: RAISE fnd_api.g_exc_unexpected_error;
274: END;
275:
276: SELECT mtl_gen_object_id_s.NEXTVAL
277: INTO x_object_id
324: , NVL(p_planning_tp_type, 2)
325: );
326: END IF;
327:
328: x_return_status := fnd_api.g_ret_sts_success;
329:
330: -- End of API body.
331: -- Standard check of p_commit.
332: IF fnd_api.to_boolean(p_commit) THEN
328: x_return_status := fnd_api.g_ret_sts_success;
329:
330: -- End of API body.
331: -- Standard check of p_commit.
332: IF fnd_api.to_boolean(p_commit) THEN
333: COMMIT WORK;
334: END IF;
335:
336: -- Standard call to get message count and if count is 1, get message info.
333: COMMIT WORK;
334: END IF;
335:
336: -- Standard call to get message count and if count is 1, get message info.
337: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
338: EXCEPTION
339: WHEN fnd_api.g_exc_error THEN
340: ROLLBACK TO apiinsertserial_apipub;
341: --Bug 3153585:Populating the message from the message stack
335:
336: -- Standard call to get message count and if count is 1, get message info.
337: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
338: EXCEPTION
339: WHEN fnd_api.g_exc_error THEN
340: ROLLBACK TO apiinsertserial_apipub;
341: --Bug 3153585:Populating the message from the message stack
342: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
343: x_return_status := fnd_api.g_ret_sts_error;
338: EXCEPTION
339: WHEN fnd_api.g_exc_error THEN
340: ROLLBACK TO apiinsertserial_apipub;
341: --Bug 3153585:Populating the message from the message stack
342: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
343: x_return_status := fnd_api.g_ret_sts_error;
344: WHEN fnd_api.g_exc_unexpected_error THEN
345: --Bug 3153585:Populating the message from the message stack
346: ROLLBACK TO apiinsertserial_apipub;
339: WHEN fnd_api.g_exc_error THEN
340: ROLLBACK TO apiinsertserial_apipub;
341: --Bug 3153585:Populating the message from the message stack
342: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
343: x_return_status := fnd_api.g_ret_sts_error;
344: WHEN fnd_api.g_exc_unexpected_error THEN
345: --Bug 3153585:Populating the message from the message stack
346: ROLLBACK TO apiinsertserial_apipub;
347: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
340: ROLLBACK TO apiinsertserial_apipub;
341: --Bug 3153585:Populating the message from the message stack
342: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
343: x_return_status := fnd_api.g_ret_sts_error;
344: WHEN fnd_api.g_exc_unexpected_error THEN
345: --Bug 3153585:Populating the message from the message stack
346: ROLLBACK TO apiinsertserial_apipub;
347: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
348: x_return_status := fnd_api.g_ret_sts_unexp_error;
343: x_return_status := fnd_api.g_ret_sts_error;
344: WHEN fnd_api.g_exc_unexpected_error THEN
345: --Bug 3153585:Populating the message from the message stack
346: ROLLBACK TO apiinsertserial_apipub;
347: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
348: x_return_status := fnd_api.g_ret_sts_unexp_error;
349: WHEN OTHERS THEN
350: ROLLBACK TO apiinsertserial_apipub;
351:
344: WHEN fnd_api.g_exc_unexpected_error THEN
345: --Bug 3153585:Populating the message from the message stack
346: ROLLBACK TO apiinsertserial_apipub;
347: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
348: x_return_status := fnd_api.g_ret_sts_unexp_error;
349: WHEN OTHERS THEN
350: ROLLBACK TO apiinsertserial_apipub;
351:
352: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
352: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
353: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
354: END IF;
355:
356: x_return_status := fnd_api.g_ret_sts_unexp_error;
357: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
358: END insertserial;
359:
360: -- 'Serial Tracking in WIP project. insert wip_entity_id, operation_seq_num and intraoperation_step_type
353: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
354: END IF;
355:
356: x_return_status := fnd_api.g_ret_sts_unexp_error;
357: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
358: END insertserial;
359:
360: -- 'Serial Tracking in WIP project. insert wip_entity_id, operation_seq_num and intraoperation_step_type
361: -- into MSN.
360: -- 'Serial Tracking in WIP project. insert wip_entity_id, operation_seq_num and intraoperation_step_type
361: -- into MSN.
362: PROCEDURE insertserial(
363: p_api_version IN NUMBER
364: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
365: , p_commit IN VARCHAR2 := fnd_api.g_false
366: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
367: , p_inventory_item_id IN NUMBER
368: , p_organization_id IN NUMBER
361: -- into MSN.
362: PROCEDURE insertserial(
363: p_api_version IN NUMBER
364: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
365: , p_commit IN VARCHAR2 := fnd_api.g_false
366: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
367: , p_inventory_item_id IN NUMBER
368: , p_organization_id IN NUMBER
369: , p_serial_number IN VARCHAR2
362: PROCEDURE insertserial(
363: p_api_version IN NUMBER
364: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
365: , p_commit IN VARCHAR2 := fnd_api.g_false
366: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
367: , p_inventory_item_id IN NUMBER
368: , p_organization_id IN NUMBER
369: , p_serial_number IN VARCHAR2
370: , p_initialization_date IN DATE
483: inv_trx_util_pub.TRACE('In insertserial() procedure. ', 'INV_SERIAL_NUMBER_PUB', 9);
484: END IF;
485:
486: -- Standard call to check for call compatibility.
487: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
488: RAISE fnd_api.g_exc_unexpected_error;
489: END IF;
490:
491: -- Initialize message list if p_init_msg_list is set to TRUE.
484: END IF;
485:
486: -- Standard call to check for call compatibility.
487: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
488: RAISE fnd_api.g_exc_unexpected_error;
489: END IF;
490:
491: -- Initialize message list if p_init_msg_list is set to TRUE.
492: IF fnd_api.to_boolean(p_init_msg_list) THEN
488: RAISE fnd_api.g_exc_unexpected_error;
489: END IF;
490:
491: -- Initialize message list if p_init_msg_list is set to TRUE.
492: IF fnd_api.to_boolean(p_init_msg_list) THEN
493: fnd_msg_pub.initialize;
494: END IF;
495:
496: /** ssia we don't need this if condition **/
495:
496: /** ssia we don't need this if condition **/
497: /*IF (p_transaction_action_id = 3 AND g_firstscan = FALSE) THEN*/
498: /*** ssia note end ****/
499: x_return_status := fnd_api.g_ret_sts_success;
500:
501: /**ELSE**/
502: BEGIN
503: SELECT serial_number_control_code
512: -- invtrace('serial_control_code is 1.there is no serial control.');
513: fnd_message.set_name('INV', 'INV_ITEM_NOT_SERIAL_CONTROLLED');
514: fnd_msg_pub.ADD;
515: --Bug 3153585:Populating the message from the message stack
516: RAISE fnd_api.g_exc_error;
517: END IF;
518: EXCEPTION
519: WHEN NO_DATA_FOUND THEN
520: fnd_message.set_name('INV', 'INV_INVALID_ITEM');
518: EXCEPTION
519: WHEN NO_DATA_FOUND THEN
520: fnd_message.set_name('INV', 'INV_INVALID_ITEM');
521: fnd_msg_pub.ADD;
522: RAISE fnd_api.g_exc_error;
523: END;
524:
525: SELECT mtl_gen_object_id_s.NEXTVAL
526: INTO x_object_id
631: , p_lot_serial_number => p_serial_number
632: , p_attributes => l_attributes_in
633: );
634:
635: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
636: x_return_status := l_return_status;
637: RAISE fnd_api.g_exc_unexpected_error;
638: END IF;
639:
633: );
634:
635: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
636: x_return_status := l_return_status;
637: RAISE fnd_api.g_exc_unexpected_error;
638: END IF;
639:
640: IF (l_attributes_default_count > 0) THEN
641: FOR i IN 1 .. l_attributes_default_count LOOP
923: , x_serial_status_enabled => l_serial_status_enabled
924: , x_default_serial_status_id => l_default_serial_status_id
925: );
926:
927: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
928: x_return_status := l_return_status;
929: RAISE fnd_api.g_exc_unexpected_error;
930: END IF;
931:
925: );
926:
927: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
928: x_return_status := l_return_status;
929: RAISE fnd_api.g_exc_unexpected_error;
930: END IF;
931:
932: IF (NVL(l_serial_status_enabled, 'Y') = 'Y') THEN
933: l_status_id := l_default_serial_status_id;
1253: -- invtrace('operation_seq_num is ' || p_operation_seq_num);
1254: -- invtrace('intraoperation_step_type is ' || p_intraoperation_step_type);
1255: END IF;
1256:
1257: x_return_status := fnd_api.g_ret_sts_success;
1258:
1259: -- insert into the status history table for bug 1870120
1260: IF (l_status_id IS NOT NULL) THEN
1261: l_status_rec.update_method := inv_material_status_pub.g_update_method_auto;
1267: inv_material_status_pkg.insert_status_history(l_status_rec);
1268: END IF;
1269:
1270: -- Standard check of p_commit.
1271: IF fnd_api.to_boolean(p_commit) THEN
1272: COMMIT WORK;
1273: END IF;
1274:
1275: -- Standard call to get message count and if count is 1, get message info.
1272: COMMIT WORK;
1273: END IF;
1274:
1275: -- Standard call to get message count and if count is 1, get message info.
1276: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1277: EXCEPTION
1278: WHEN fnd_api.g_exc_error THEN
1279: ROLLBACK TO apiinsertserial_apipub;
1280: --Bug 3153585:Populating x_msg_data from message stack
1274:
1275: -- Standard call to get message count and if count is 1, get message info.
1276: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1277: EXCEPTION
1278: WHEN fnd_api.g_exc_error THEN
1279: ROLLBACK TO apiinsertserial_apipub;
1280: --Bug 3153585:Populating x_msg_data from message stack
1281: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1282: x_return_status := fnd_api.g_ret_sts_error;
1277: EXCEPTION
1278: WHEN fnd_api.g_exc_error THEN
1279: ROLLBACK TO apiinsertserial_apipub;
1280: --Bug 3153585:Populating x_msg_data from message stack
1281: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1282: x_return_status := fnd_api.g_ret_sts_error;
1283: WHEN OTHERS THEN
1284: ROLLBACK TO apiinsertserial_apipub;
1285:
1278: WHEN fnd_api.g_exc_error THEN
1279: ROLLBACK TO apiinsertserial_apipub;
1280: --Bug 3153585:Populating x_msg_data from message stack
1281: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1282: x_return_status := fnd_api.g_ret_sts_error;
1283: WHEN OTHERS THEN
1284: ROLLBACK TO apiinsertserial_apipub;
1285:
1286: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1287: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
1288: END IF;
1289:
1290: --Bug 3153585:Populating x_msg_data from message stack
1291: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1292: x_return_status := fnd_api.g_ret_sts_unexp_error;
1293: END insertserial;
1294:
1295: -- Procedure insertSerial for Serial Blacklist
1288: END IF;
1289:
1290: --Bug 3153585:Populating x_msg_data from message stack
1291: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1292: x_return_status := fnd_api.g_ret_sts_unexp_error;
1293: END insertserial;
1294:
1295: -- Procedure insertSerial for Serial Blacklist
1296: PROCEDURE insert_range_serial(
1294:
1295: -- Procedure insertSerial for Serial Blacklist
1296: PROCEDURE insert_range_serial(
1297: p_api_version IN NUMBER
1298: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1299: , p_commit IN VARCHAR2 := fnd_api.g_false
1300: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1301: , p_inventory_item_id IN NUMBER
1302: , p_organization_id IN NUMBER
1295: -- Procedure insertSerial for Serial Blacklist
1296: PROCEDURE insert_range_serial(
1297: p_api_version IN NUMBER
1298: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1299: , p_commit IN VARCHAR2 := fnd_api.g_false
1300: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1301: , p_inventory_item_id IN NUMBER
1302: , p_organization_id IN NUMBER
1303: , p_fm_serial_number IN VARCHAR2
1296: PROCEDURE insert_range_serial(
1297: p_api_version IN NUMBER
1298: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1299: , p_commit IN VARCHAR2 := fnd_api.g_false
1300: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1301: , p_inventory_item_id IN NUMBER
1302: , p_organization_id IN NUMBER
1303: , p_fm_serial_number IN VARCHAR2
1304: , p_to_serial_number IN VARCHAR2
1341: -- Standard Start of API savepoint
1342: SAVEPOINT apiinsertserial_apipub;
1343:
1344: -- Standard call to check for call compatibility.
1345: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1346: RAISE fnd_api.g_exc_unexpected_error;
1347: END IF;
1348:
1349: -- Initialize message list if p_init_msg_list is set to TRUE.
1342: SAVEPOINT apiinsertserial_apipub;
1343:
1344: -- Standard call to check for call compatibility.
1345: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1346: RAISE fnd_api.g_exc_unexpected_error;
1347: END IF;
1348:
1349: -- Initialize message list if p_init_msg_list is set to TRUE.
1350: IF fnd_api.to_boolean(p_init_msg_list) THEN
1346: RAISE fnd_api.g_exc_unexpected_error;
1347: END IF;
1348:
1349: -- Initialize message list if p_init_msg_list is set to TRUE.
1350: IF fnd_api.to_boolean(p_init_msg_list) THEN
1351: fnd_msg_pub.initialize;
1352: END IF;
1353:
1354: -- Initialize API return status to success
1367: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1368: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insert_range_serial');
1369: END IF;
1370:
1371: RAISE fnd_api.g_exc_unexpected_error;
1372: END;
1373:
1374: IF (item_count = 0) THEN
1375: fnd_message.set_name('INV', 'INVALID ORGANIZATION');
1373:
1374: IF (item_count = 0) THEN
1375: fnd_message.set_name('INV', 'INVALID ORGANIZATION');
1376: fnd_msg_pub.ADD;
1377: RAISE fnd_api.g_exc_error;
1378: END IF;
1379:
1380: -- Block to check the Serial Control Code
1381: BEGIN
1387:
1388: IF (l_serial_control_code = 1) THEN
1389: fnd_message.set_name('INV', 'INV_ITEM_NOT_SERIAL_CONTROLLED');
1390: fnd_msg_pub.ADD;
1391: RAISE fnd_api.g_exc_error;
1392: END IF;
1393:
1394: l_current_status := nvl(p_current_status,1);
1395: --bug10074753,we should populate the validate serial current status.
1396: IF(l_current_status NOT IN(1,3,4,5)) THEN
1397: fnd_message.set_name('INV', 'INV_SER_INVALID_STATUS');
1398: fnd_message.set_token('TOKEN',' ');
1399: fnd_msg_pub.ADD;
1400: RAISE fnd_api.g_exc_error;
1401: END IF;
1402:
1403: EXCEPTION
1404: WHEN fnd_api.g_exc_error THEN
1400: RAISE fnd_api.g_exc_error;
1401: END IF;
1402:
1403: EXCEPTION
1404: WHEN fnd_api.g_exc_error THEN
1405: RAISE fnd_api.g_exc_error;
1406: WHEN NO_DATA_FOUND THEN
1407: fnd_message.set_name('INV', 'INV_INVALID_ITEM');
1408: fnd_msg_pub.ADD;
1401: END IF;
1402:
1403: EXCEPTION
1404: WHEN fnd_api.g_exc_error THEN
1405: RAISE fnd_api.g_exc_error;
1406: WHEN NO_DATA_FOUND THEN
1407: fnd_message.set_name('INV', 'INV_INVALID_ITEM');
1408: fnd_msg_pub.ADD;
1409: RAISE fnd_api.g_exc_error;
1405: RAISE fnd_api.g_exc_error;
1406: WHEN NO_DATA_FOUND THEN
1407: fnd_message.set_name('INV', 'INV_INVALID_ITEM');
1408: fnd_msg_pub.ADD;
1409: RAISE fnd_api.g_exc_error;
1410: WHEN OTHERS THEN
1411: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1412: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insert_range_serial');
1413: END IF;
1411: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1412: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insert_range_serial');
1413: END IF;
1414:
1415: RAISE fnd_api.g_exc_unexpected_error;
1416: END;
1417: --Block for material status validation
1418: BEGIN
1419: SELECT serial_control into l_serial_status_control
1423: --If not then raise an error.
1424: IF (l_serial_status_control <> 1) THEN
1425: fnd_message.set_name('INV','INV_STATUS_NOT_APP'); --error messag
1426: fnd_msg_pub.ADD;
1427: RAISE fnd_api.g_exc_error;
1428: END IF;
1429:
1430: EXCEPTION
1431: WHEN fnd_api.g_exc_error THEN
1427: RAISE fnd_api.g_exc_error;
1428: END IF;
1429:
1430: EXCEPTION
1431: WHEN fnd_api.g_exc_error THEN
1432: RAISE fnd_api.g_exc_error;
1433: WHEN NO_DATA_FOUND THEN
1434: fnd_message.set_name('INV', 'INV_STATUS_NOT_APP');
1435: fnd_msg_pub.ADD;
1428: END IF;
1429:
1430: EXCEPTION
1431: WHEN fnd_api.g_exc_error THEN
1432: RAISE fnd_api.g_exc_error;
1433: WHEN NO_DATA_FOUND THEN
1434: fnd_message.set_name('INV', 'INV_STATUS_NOT_APP');
1435: fnd_msg_pub.ADD;
1436: RAISE fnd_api.g_exc_error;
1432: RAISE fnd_api.g_exc_error;
1433: WHEN NO_DATA_FOUND THEN
1434: fnd_message.set_name('INV', 'INV_STATUS_NOT_APP');
1435: fnd_msg_pub.ADD;
1436: RAISE fnd_api.g_exc_error;
1437: WHEN OTHERS THEN
1438: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1439: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insert_range_serial');
1440: END IF;
1437: WHEN OTHERS THEN
1438: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1439: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insert_range_serial');
1440: END IF;
1441: RAISE fnd_api.g_exc_unexpected_error;
1442: END;
1443: --bug10074736,we should make sure the validate lot number here.
1444: --Block for lot number validation
1445: BEGIN
1454:
1455: IF (l_lot_exists = 0) THEN
1456: fnd_message.set_name('INV','INV_INVALID_LOT'); --error messag
1457: fnd_msg_pub.ADD;
1458: RAISE fnd_api.g_exc_error;
1459: END IF;
1460: END IF;
1461: EXCEPTION
1462: WHEN fnd_api.g_exc_error THEN
1458: RAISE fnd_api.g_exc_error;
1459: END IF;
1460: END IF;
1461: EXCEPTION
1462: WHEN fnd_api.g_exc_error THEN
1463: RAISE fnd_api.g_exc_error;
1464: WHEN NO_DATA_FOUND THEN
1465: l_lot_exists := 0;
1466: fnd_message.set_name('INV','INV_INVALID_LOT'); --error messag
1459: END IF;
1460: END IF;
1461: EXCEPTION
1462: WHEN fnd_api.g_exc_error THEN
1463: RAISE fnd_api.g_exc_error;
1464: WHEN NO_DATA_FOUND THEN
1465: l_lot_exists := 0;
1466: fnd_message.set_name('INV','INV_INVALID_LOT'); --error messag
1467: fnd_msg_pub.ADD;
1464: WHEN NO_DATA_FOUND THEN
1465: l_lot_exists := 0;
1466: fnd_message.set_name('INV','INV_INVALID_LOT'); --error messag
1467: fnd_msg_pub.ADD;
1468: RAISE fnd_api.g_exc_error;
1469: WHEN OTHERS THEN
1470: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1471: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insert_range_serial');
1472: END IF;
1469: WHEN OTHERS THEN
1470: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1471: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insert_range_serial');
1472: END IF;
1473: RAISE fnd_api.g_exc_unexpected_error;
1474: END;
1475: --bug10074682,validate the from serial number and to serial number available.
1476: --Block for from serial number and to serial number validation,it should be varchar.
1477: IF nvl(p_fm_serial_number,'!@') = '!@' OR nvl(p_to_serial_number,'!@') = '!@' THEN
1476: --Block for from serial number and to serial number validation,it should be varchar.
1477: IF nvl(p_fm_serial_number,'!@') = '!@' OR nvl(p_to_serial_number,'!@') = '!@' THEN
1478: fnd_message.set_name('INV','INVALID_SERIAL_NUMBER'); --error messag
1479: fnd_msg_pub.ADD;
1480: RAISE fnd_api.g_exc_error;
1481: END IF;
1482: -- get the number part of the from serial
1483: inv_validate.number_from_sequence(p_fm_serial_number, l_temp_prefix, l_from_ser_number);
1484: -- get the number part of the to serial
1488: --bug10074682,from serial number and to serial number shoule be in sequence.
1489: IF l_range_numbers < 1 THEN --the range numbers with no sense,need enter the validate range nubmers
1490: fnd_message.set_name('INV','INVALID_SERIAL_NUMBER'); --error messag
1491: fnd_msg_pub.ADD;
1492: RAISE fnd_api.g_exc_error;
1493: END IF;
1494: FOR i IN 1 .. l_range_numbers LOOP
1495: l_cur_ser_number := l_from_ser_number + i - 1;
1496: -- concatenate the serial number to be inserted
1554: );
1555: END IF;
1556: END LOOP;
1557:
1558: x_return_status := fnd_api.g_ret_sts_success;
1559: -- End of API body.
1560: -- Standard check of p_commit.
1561: IF fnd_api.to_boolean(p_commit) THEN
1562: COMMIT WORK;
1557:
1558: x_return_status := fnd_api.g_ret_sts_success;
1559: -- End of API body.
1560: -- Standard check of p_commit.
1561: IF fnd_api.to_boolean(p_commit) THEN
1562: COMMIT WORK;
1563: END IF;
1564: -- Standard call to get message count and if count is 1, get message info.
1565: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1561: IF fnd_api.to_boolean(p_commit) THEN
1562: COMMIT WORK;
1563: END IF;
1564: -- Standard call to get message count and if count is 1, get message info.
1565: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1566: EXCEPTION
1567: WHEN fnd_api.g_exc_error THEN
1568: ROLLBACK TO apiinsertserial_apipub;
1569: --Bug 3153585:Populating x_msg_data from message stack
1563: END IF;
1564: -- Standard call to get message count and if count is 1, get message info.
1565: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1566: EXCEPTION
1567: WHEN fnd_api.g_exc_error THEN
1568: ROLLBACK TO apiinsertserial_apipub;
1569: --Bug 3153585:Populating x_msg_data from message stack
1570: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1571: x_return_status := fnd_api.g_ret_sts_error;
1566: EXCEPTION
1567: WHEN fnd_api.g_exc_error THEN
1568: ROLLBACK TO apiinsertserial_apipub;
1569: --Bug 3153585:Populating x_msg_data from message stack
1570: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1571: x_return_status := fnd_api.g_ret_sts_error;
1572: WHEN OTHERS THEN
1573: ROLLBACK TO apiinsertserial_apipub;
1574:
1567: WHEN fnd_api.g_exc_error THEN
1568: ROLLBACK TO apiinsertserial_apipub;
1569: --Bug 3153585:Populating x_msg_data from message stack
1570: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1571: x_return_status := fnd_api.g_ret_sts_error;
1572: WHEN OTHERS THEN
1573: ROLLBACK TO apiinsertserial_apipub;
1574:
1575: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1576: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insert_range_serial');
1577: END IF;
1578:
1579: --Bug 3153585:Populating x_msg_data from message stack
1580: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1581: x_return_status := fnd_api.g_ret_sts_unexp_error;
1582:
1583: END insert_range_serial;
1584:
1577: END IF;
1578:
1579: --Bug 3153585:Populating x_msg_data from message stack
1580: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1581: x_return_status := fnd_api.g_ret_sts_unexp_error;
1582:
1583: END insert_range_serial;
1584:
1585: PROCEDURE insert_range_serial(
1583: END insert_range_serial;
1584:
1585: PROCEDURE insert_range_serial(
1586: p_api_version IN NUMBER
1587: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1588: , p_commit IN VARCHAR2 := fnd_api.g_false
1589: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1590: , p_inventory_item_id IN NUMBER
1591: , p_organization_id IN NUMBER
1584:
1585: PROCEDURE insert_range_serial(
1586: p_api_version IN NUMBER
1587: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1588: , p_commit IN VARCHAR2 := fnd_api.g_false
1589: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1590: , p_inventory_item_id IN NUMBER
1591: , p_organization_id IN NUMBER
1592: , p_from_serial_number IN VARCHAR2
1585: PROCEDURE insert_range_serial(
1586: p_api_version IN NUMBER
1587: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1588: , p_commit IN VARCHAR2 := fnd_api.g_false
1589: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1590: , p_inventory_item_id IN NUMBER
1591: , p_organization_id IN NUMBER
1592: , p_from_serial_number IN VARCHAR2
1593: , p_to_serial_number IN VARCHAR2
1632: l_temp_prefix VARCHAR2(30);
1633: l_cur_serial_number VARCHAR2(30);
1634: l_cur_ser_number NUMBER;
1635: l_object_id NUMBER;
1636: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1637: l_msg_count NUMBER;
1638: l_msg_data VARCHAR2(2000);
1639: l_current_status NUMBER;
1640: l_group_mark_id NUMBER;
1646: -- invtrace('INV_SERIAL_NUMBER_PUB', 'insert_range_serial - 10');
1647: SAVEPOINT sp_insert_range_serial;
1648:
1649: -- Standard call to check for call compatibility.
1650: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1651: RAISE fnd_api.g_exc_unexpected_error;
1652: END IF;
1653:
1654: -- Initialize message list if p_init_msg_list is set to TRUE.
1647: SAVEPOINT sp_insert_range_serial;
1648:
1649: -- Standard call to check for call compatibility.
1650: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1651: RAISE fnd_api.g_exc_unexpected_error;
1652: END IF;
1653:
1654: -- Initialize message list if p_init_msg_list is set to TRUE.
1655: IF fnd_api.to_boolean(p_init_msg_list) THEN
1651: RAISE fnd_api.g_exc_unexpected_error;
1652: END IF;
1653:
1654: -- Initialize message list if p_init_msg_list is set to TRUE.
1655: IF fnd_api.to_boolean(p_init_msg_list) THEN
1656: fnd_msg_pub.initialize;
1657: END IF;
1658:
1659: -- Initialize API return status to success
1656: fnd_msg_pub.initialize;
1657: END IF;
1658:
1659: -- Initialize API return status to success
1660: x_return_status := fnd_api.g_ret_sts_success;
1661: -- get the number part of the from serial
1662: inv_validate.number_from_sequence(p_from_serial_number, l_temp_prefix, l_from_ser_number);
1663: -- get the number part of the to serial
1664: inv_validate.number_from_sequence(p_to_serial_number, l_temp_prefix, l_to_ser_number);
1747: IF is_serial_unique(p_org_id => p_organization_id, p_item_id => p_inventory_item_id, p_serial => l_cur_serial_number
1748: , x_proc_msg => l_msg_data) = 1 THEN
1749: fnd_message.set_name('INV', 'INV_SERIAL_USED');
1750: fnd_msg_pub.ADD;
1751: RAISE fnd_api.g_exc_error;
1752: ELSE
1753: -- invtrace('INV_SERIAL_NUMBER_PUB', 'insert_range_serial - 60');
1754: -- uniqueness check passed
1755: -- and it is not a pre-defined serial
1794: , p_planning_org_id => p_planning_org_id
1795: , p_planning_tp_type => p_planning_tp_type
1796: );
1797:
1798: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1799: fnd_message.set_name('INV', 'INV_SERIAL');
1800: fnd_msg_pub.ADD;
1801: RAISE fnd_api.g_exc_unexpected_error;
1802: END IF;
1797:
1798: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1799: fnd_message.set_name('INV', 'INV_SERIAL');
1800: fnd_msg_pub.ADD;
1801: RAISE fnd_api.g_exc_unexpected_error;
1802: END IF;
1803: END IF;
1804: END IF;
1805:
1836: AND current_status IN(1, 4, 5, 6);
1837: END IF;
1838: END LOOP;
1839:
1840: x_return_status := fnd_api.g_ret_sts_success;
1841:
1842: -- End of API body
1843: -- Standard check of p_commit.
1844: IF fnd_api.to_boolean(p_commit) THEN
1840: x_return_status := fnd_api.g_ret_sts_success;
1841:
1842: -- End of API body
1843: -- Standard check of p_commit.
1844: IF fnd_api.to_boolean(p_commit) THEN
1845: COMMIT WORK;
1846: END IF;
1847:
1848: -- Standard call to get message count and if count is 1,
1848: -- Standard call to get message count and if count is 1,
1849: -- get message info.
1850: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1851: EXCEPTION
1852: WHEN fnd_api.g_exc_error THEN
1853: ROLLBACK TO sp_insert_range_serial;
1854: x_return_status := fnd_api.g_ret_sts_error;
1855: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1856: WHEN fnd_api.g_exc_unexpected_error THEN
1850: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1851: EXCEPTION
1852: WHEN fnd_api.g_exc_error THEN
1853: ROLLBACK TO sp_insert_range_serial;
1854: x_return_status := fnd_api.g_ret_sts_error;
1855: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1856: WHEN fnd_api.g_exc_unexpected_error THEN
1857: ROLLBACK TO sp_insert_range_serial;
1858: x_return_status := fnd_api.g_ret_sts_unexp_error;
1852: WHEN fnd_api.g_exc_error THEN
1853: ROLLBACK TO sp_insert_range_serial;
1854: x_return_status := fnd_api.g_ret_sts_error;
1855: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1856: WHEN fnd_api.g_exc_unexpected_error THEN
1857: ROLLBACK TO sp_insert_range_serial;
1858: x_return_status := fnd_api.g_ret_sts_unexp_error;
1859: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1860: WHEN OTHERS THEN
1854: x_return_status := fnd_api.g_ret_sts_error;
1855: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1856: WHEN fnd_api.g_exc_unexpected_error THEN
1857: ROLLBACK TO sp_insert_range_serial;
1858: x_return_status := fnd_api.g_ret_sts_unexp_error;
1859: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1860: WHEN OTHERS THEN
1861: ROLLBACK TO sp_insert_range_serial;
1862: x_return_status := fnd_api.g_ret_sts_unexp_error;
1858: x_return_status := fnd_api.g_ret_sts_unexp_error;
1859: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1860: WHEN OTHERS THEN
1861: ROLLBACK TO sp_insert_range_serial;
1862: x_return_status := fnd_api.g_ret_sts_unexp_error;
1863: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1864: END insert_range_serial;
1865:
1866: PROCEDURE updateserial(
1864: END insert_range_serial;
1865:
1866: PROCEDURE updateserial(
1867: p_api_version IN NUMBER
1868: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1869: , p_commit IN VARCHAR2 := fnd_api.g_false
1870: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1871: , p_inventory_item_id IN NUMBER
1872: , p_organization_id IN NUMBER
1865:
1866: PROCEDURE updateserial(
1867: p_api_version IN NUMBER
1868: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1869: , p_commit IN VARCHAR2 := fnd_api.g_false
1870: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1871: , p_inventory_item_id IN NUMBER
1872: , p_organization_id IN NUMBER
1873: , p_serial_number IN VARCHAR2
1866: PROCEDURE updateserial(
1867: p_api_version IN NUMBER
1868: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1869: , p_commit IN VARCHAR2 := fnd_api.g_false
1870: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1871: , p_inventory_item_id IN NUMBER
1872: , p_organization_id IN NUMBER
1873: , p_serial_number IN VARCHAR2
1874: , p_initialization_date IN DATE
2017: invtrace('*** Inside UpdateSerial ****');
2018: END IF;
2019:
2020: -- Standard call to check for call compatibility.
2021: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2022: RAISE fnd_api.g_exc_unexpected_error;
2023: END IF;
2024:
2025: -- Initialize message list if p_init_msg_list is set to TRUE.
2018: END IF;
2019:
2020: -- Standard call to check for call compatibility.
2021: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2022: RAISE fnd_api.g_exc_unexpected_error;
2023: END IF;
2024:
2025: -- Initialize message list if p_init_msg_list is set to TRUE.
2026: IF fnd_api.to_boolean(p_init_msg_list) THEN
2022: RAISE fnd_api.g_exc_unexpected_error;
2023: END IF;
2024:
2025: -- Initialize message list if p_init_msg_list is set to TRUE.
2026: IF fnd_api.to_boolean(p_init_msg_list) THEN
2027: fnd_msg_pub.initialize;
2028: END IF;
2029:
2030: -- Initialize API return status to success
2027: fnd_msg_pub.initialize;
2028: END IF;
2029:
2030: -- Initialize API return status to success
2031: x_return_status := fnd_api.g_ret_sts_success;
2032: -- API body
2033:
2034: IF (l_debug = 1) THEN
2035: invtrace('CurStat =' || p_current_status || ',CG=' ||
2363: AND serial_number = p_serial_number
2364: AND DECODE(current_status, 6, 1, current_status) = DECODE(p_last_status, 6, 1, p_last_status);
2365: END IF;
2366:
2367: x_return_status := fnd_api.g_ret_sts_success;
2368:
2369: -- End of API body.
2370: -- Standard check of p_commit.
2371: IF fnd_api.to_boolean(p_commit) THEN
2367: x_return_status := fnd_api.g_ret_sts_success;
2368:
2369: -- End of API body.
2370: -- Standard check of p_commit.
2371: IF fnd_api.to_boolean(p_commit) THEN
2372: COMMIT WORK;
2373: END IF;
2374:
2375: -- Standard call to get message count and if count is 1, get message info.
2372: COMMIT WORK;
2373: END IF;
2374:
2375: -- Standard call to get message count and if count is 1, get message info.
2376: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2377: EXCEPTION
2378: WHEN OTHERS THEN
2379: ROLLBACK TO apiupdateserial_apipub;
2380:
2381: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2382: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'updateSerial');
2383: END IF;
2384:
2385: x_return_status := fnd_api.g_ret_sts_unexp_error;
2386: END updateserial;
2387:
2388: PROCEDURE insertunittrx(
2389: p_api_version IN NUMBER
2386: END updateserial;
2387:
2388: PROCEDURE insertunittrx(
2389: p_api_version IN NUMBER
2390: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2391: , p_commit IN VARCHAR2 := fnd_api.g_false
2392: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2393: , p_inventory_item_id IN NUMBER
2394: , p_organization_id IN NUMBER
2387:
2388: PROCEDURE insertunittrx(
2389: p_api_version IN NUMBER
2390: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2391: , p_commit IN VARCHAR2 := fnd_api.g_false
2392: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2393: , p_inventory_item_id IN NUMBER
2394: , p_organization_id IN NUMBER
2395: , p_serial_number IN VARCHAR2
2388: PROCEDURE insertunittrx(
2389: p_api_version IN NUMBER
2390: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2391: , p_commit IN VARCHAR2 := fnd_api.g_false
2392: , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2393: , p_inventory_item_id IN NUMBER
2394: , p_organization_id IN NUMBER
2395: , p_serial_number IN VARCHAR2
2396: , p_current_locator_id IN NUMBER
2504: -- Standard Start of API savepoint
2505: SAVEPOINT apiinsertserial_apipub;
2506:
2507: -- Standard call to check for call compatibility.
2508: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2509: RAISE fnd_api.g_exc_unexpected_error;
2510: END IF;
2511:
2512: -- Initialize message list if p_init_msg_list is set to TRUE.
2505: SAVEPOINT apiinsertserial_apipub;
2506:
2507: -- Standard call to check for call compatibility.
2508: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2509: RAISE fnd_api.g_exc_unexpected_error;
2510: END IF;
2511:
2512: -- Initialize message list if p_init_msg_list is set to TRUE.
2513: IF fnd_api.to_boolean(p_init_msg_list) THEN
2509: RAISE fnd_api.g_exc_unexpected_error;
2510: END IF;
2511:
2512: -- Initialize message list if p_init_msg_list is set to TRUE.
2513: IF fnd_api.to_boolean(p_init_msg_list) THEN
2514: fnd_msg_pub.initialize;
2515: END IF;
2516:
2517: -- Initialize API return status to success
2516:
2517: -- Initialize API return status to success
2518:
2519: /**IF (p_transaction_action_id = 3 AND g_firstscan = FALSE) THEN
2520: x_return_status := fnd_api.g_ret_sts_success;
2521: ELSE**/
2522: x_return_status := fnd_api.g_ret_sts_success;
2523: l_wms_installed :=
2524: wms_install.check_install(
2518:
2519: /**IF (p_transaction_action_id = 3 AND g_firstscan = FALSE) THEN
2520: x_return_status := fnd_api.g_ret_sts_success;
2521: ELSE**/
2522: x_return_status := fnd_api.g_ret_sts_success;
2523: l_wms_installed :=
2524: wms_install.check_install(
2525: x_return_status => l_return_status
2526: , x_msg_count => l_msg_count
2613: , p_lot_serial_number => p_serial_number
2614: , p_attributes => l_attributes_in
2615: );
2616:
2617: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2618: x_return_status := l_return_status;
2619: RAISE fnd_api.g_exc_unexpected_error;
2620: END IF;
2621:
2615: );
2616:
2617: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2618: x_return_status := l_return_status;
2619: RAISE fnd_api.g_exc_unexpected_error;
2620: END IF;
2621:
2622: /* Get the default attribs only when there is no value on the form (in MSNT).
2623: * In case the user changes the context and the attributes while recieving,
3309: END IF;
3310: END;
3311: END IF;
3312:
3313: x_return_status := fnd_api.g_ret_sts_success;
3314:
3315: -- End of API body.
3316: -- Standard check of p_commit.
3317: IF fnd_api.to_boolean(p_commit) THEN
3313: x_return_status := fnd_api.g_ret_sts_success;
3314:
3315: -- End of API body.
3316: -- Standard check of p_commit.
3317: IF fnd_api.to_boolean(p_commit) THEN
3318: COMMIT WORK;
3319: END IF;
3320:
3321: -- Standard call to get message count and if count is 1, get message info.
3318: COMMIT WORK;
3319: END IF;
3320:
3321: -- Standard call to get message count and if count is 1, get message info.
3322: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_msg_data);
3323: x_msg_data := SUBSTR(l_msg_data, 0, 198);
3324: EXCEPTION
3325: WHEN OTHERS THEN
3326: ROLLBACK TO apiinsertserial_apipub;
3328: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3329: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertUnitTrx');
3330: END IF;
3331:
3332: x_return_status := fnd_api.g_ret_sts_unexp_error;
3333: END insertunittrx;
3334:
3335: ------------------------------------------------------------------------------
3336: -- Name: GENERATE_SERIALSJ
3407: , p_group_mark_id IN NUMBER DEFAULT NULL
3408: , p_line_mark_id IN NUMBER DEFAULT NULL
3409: ) IS
3410: PRAGMA AUTONOMOUS_TRANSACTION;
3411: v_commit VARCHAR2(12) := fnd_api.g_true;
3412: v_mesg VARCHAR2(2000);
3413: l_start_ser VARCHAR2(100);
3414: l_end_ser VARCHAR2(100);
3415: v_retval NUMBER;
3448: COMMIT;
3449: EXCEPTION
3450: WHEN OTHERS THEN
3451: x_retcode := 2;
3452: x_errbuf := SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 250);
3453: ret := fnd_concurrent.set_completion_status('ERROR', v_mesg);
3454: RAISE;
3455: END generate_serials;
3456:
3735: IF (l_debug = 1) THEN
3736: invtrace('VALIDATE_UPDATE_SERIAL_ATT:Entered...');
3737: END IF;
3738:
3739: x_return_status := FND_API.G_RET_STS_SUCCESS;
3740:
3741: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3742:
3743: IF (l_debug = 1) THEN
3799: p_organization_id => p_organization_id,
3800: p_lot_serial_number => p_serial_number,
3801: p_attributes => g_serial_attributes_tbl);
3802:
3803: IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3804: x_validation_status := 'N';
3805: x_return_status := l_return_status;
3806: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3807: END IF;
3802:
3803: IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3804: x_validation_status := 'N';
3805: x_return_status := l_return_status;
3806: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3807: END IF;
3808:
3809: IF (l_debug = 1) THEN
3810: invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_attributes_default_count='||l_attributes_default_count);
3978: IF (l_debug = 1) THEN
3979: invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_status is FALSE');
3980: END IF;
3981: l_validation_status := 'N';
3982: x_return_status := FND_API.G_RET_STS_ERROR ;
3983: x_msg_data := fnd_flex_descval.error_message;
3984: fnd_message.set_name('INV', 'GENERIC');
3985: fnd_message.set_token('MSGBODY', x_msg_data );
3986: fnd_msg_pub.ADD;
3984: fnd_message.set_name('INV', 'GENERIC');
3985: fnd_message.set_token('MSGBODY', x_msg_data );
3986: fnd_msg_pub.ADD;
3987: x_msg_count := nvl(x_msg_count,0) + 1 ;
3988: RAISE FND_API.G_EXC_ERROR;
3989: END IF;
3990: END IF; -- if l_context_value is not null
3991:
3992: x_validation_status := l_validation_status;
4070: invtrace('VALIDATE_UPDATE_SERIAL_ATT:Exitting...');
4071: END IF;
4072:
4073: EXCEPTION
4074: WHEN FND_API.G_EXC_ERROR THEN
4075: x_validation_status := l_validation_status;
4076: x_return_status := FND_API.G_RET_STS_ERROR;
4077: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4078: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4072:
4073: EXCEPTION
4074: WHEN FND_API.G_EXC_ERROR THEN
4075: x_validation_status := l_validation_status;
4076: x_return_status := FND_API.G_RET_STS_ERROR;
4077: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4078: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4079: x_validation_status := l_validation_status;
4080: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4074: WHEN FND_API.G_EXC_ERROR THEN
4075: x_validation_status := l_validation_status;
4076: x_return_status := FND_API.G_RET_STS_ERROR;
4077: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4078: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4079: x_validation_status := l_validation_status;
4080: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4081: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4082: WHEN OTHERS THEN
4076: x_return_status := FND_API.G_RET_STS_ERROR;
4077: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4078: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4079: x_validation_status := l_validation_status;
4080: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4081: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4082: WHEN OTHERS THEN
4083: x_validation_status := l_validation_status;
4084: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4080: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4081: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4082: WHEN OTHERS THEN
4083: x_validation_status := l_validation_status;
4084: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4085: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4086: FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Validate_Attributes');
4087: END IF;
4088: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4234: IF (l_debug = 1) THEN
4235: invtrace('The transaction action is staging transfer. Bulk processing of serials are not supported for this transaction');
4236:
4237: END IF;
4238: RAISE FND_API.g_exc_unexpected_error;
4239: END IF;
4240:
4241: IF nvl(fnd_profile.value('INV_RESTRICT_RCPT_SER'), '2') = '1' THEN
4242: l_acct_prof_Value := 'Y';
4312: if( l_status = FALSE ) THEN
4313: IF (l_debug = 1) THEN
4314: invtrace('error from SNGetMask');
4315: END IF;
4316: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4317: end if;
4318:
4319: IF (l_debug = 1) THEN
4320: invtrace('l_to_status is ' || l_to_status);
4505: x_msg_count := 0;
4506: x_msg_data := NULL;
4507: END IF;
4508: EXCEPTION
4509: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4510: x_return_status := 'U';
4511: x_msg_data := substr(sqlerrm, 1, 255);
4512: x_msg_count := 1;
4513: IF (l_debug = 1) THEN
5147: END valsn;
5148:
5149: PROCEDURE insertRangeUnitTrx(
5150: p_api_version IN NUMBER,
5151: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5152: p_commit IN VARCHAR2 := FND_API.G_FALSE,
5153: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5154: p_inventory_item_id IN NUMBER,
5155: p_organization_id IN NUMBER,
5148:
5149: PROCEDURE insertRangeUnitTrx(
5150: p_api_version IN NUMBER,
5151: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5152: p_commit IN VARCHAR2 := FND_API.G_FALSE,
5153: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5154: p_inventory_item_id IN NUMBER,
5155: p_organization_id IN NUMBER,
5156: p_fm_serial_number IN VARCHAR2,
5149: PROCEDURE insertRangeUnitTrx(
5150: p_api_version IN NUMBER,
5151: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5152: p_commit IN VARCHAR2 := FND_API.G_FALSE,
5153: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5154: p_inventory_item_id IN NUMBER,
5155: p_organization_id IN NUMBER,
5156: p_fm_serial_number IN VARCHAR2,
5157: p_to_serial_number IN VARCHAR2,
5315: ELSE
5316: IF ( p_transaction_temp_id <> g_first_row_trx_tmp_id ) THEN
5317: g_first_row_trx_tmp_id := p_transaction_temp_id;
5318: ELSE
5319: x_return_status := FND_API.G_RET_STS_SUCCESS;
5320: RETURN;
5321: END IF;
5322: END IF;
5323: */
5324: -- Standard Start of API savepoint
5325: SAVEPOINT apiinsertserial_apipub;
5326:
5327: -- Standard call to check for call compatibility.
5328: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
5329: RAISE fnd_api.g_exc_unexpected_error;
5330: END IF;
5331: -- Initialize message list if p_init_msg_list is set to TRUE.
5332: IF fnd_api.to_boolean(p_init_msg_list) THEN
5325: SAVEPOINT apiinsertserial_apipub;
5326:
5327: -- Standard call to check for call compatibility.
5328: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
5329: RAISE fnd_api.g_exc_unexpected_error;
5330: END IF;
5331: -- Initialize message list if p_init_msg_list is set to TRUE.
5332: IF fnd_api.to_boolean(p_init_msg_list) THEN
5333: fnd_msg_pub.initialize;
5328: IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
5329: RAISE fnd_api.g_exc_unexpected_error;
5330: END IF;
5331: -- Initialize message list if p_init_msg_list is set to TRUE.
5332: IF fnd_api.to_boolean(p_init_msg_list) THEN
5333: fnd_msg_pub.initialize;
5334: END IF;
5335:
5336: -- Initialize API return status to success
5335:
5336: -- Initialize API return status to success
5337:
5338: /**IF (p_transaction_action_id = 3 AND g_firstscan = FALSE) THEN
5339: x_return_status := fnd_api.g_ret_sts_success;
5340: ELSE**/
5341: x_return_status := fnd_api.g_ret_sts_success;
5342: l_wms_installed :=
5343: wms_install.check_install(
5337:
5338: /**IF (p_transaction_action_id = 3 AND g_firstscan = FALSE) THEN
5339: x_return_status := fnd_api.g_ret_sts_success;
5340: ELSE**/
5341: x_return_status := fnd_api.g_ret_sts_success;
5342: l_wms_installed :=
5343: wms_install.check_install(
5344: x_return_status => l_return_status
5345: , x_msg_count => l_msg_count
5439: , p_lot_serial_number => p_fm_serial_number
5440: , p_attributes => l_attributes_in
5441: );
5442:
5443: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
5444: x_return_status := l_return_status;
5445: RAISE fnd_api.g_exc_unexpected_error;
5446: END IF;
5447:
5441: );
5442:
5443: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
5444: x_return_status := l_return_status;
5445: RAISE fnd_api.g_exc_unexpected_error;
5446: END IF;
5447:
5448: /* Get the default attribs only when there is no value on the form (in MSNT).
5449: * In case the user changes the context and the attributes while recieving,
6403:
6404: END;
6405: END IF;
6406:
6407: x_return_status := fnd_api.g_ret_sts_success;
6408: -- End of API body.
6409: -- Standard check of p_commit.
6410: IF fnd_api.to_boolean(p_commit) THEN
6411: COMMIT WORK;
6406:
6407: x_return_status := fnd_api.g_ret_sts_success;
6408: -- End of API body.
6409: -- Standard check of p_commit.
6410: IF fnd_api.to_boolean(p_commit) THEN
6411: COMMIT WORK;
6412: END IF;
6413:
6414: -- Standard call to get message count and if count is 1, get message info.
6411: COMMIT WORK;
6412: END IF;
6413:
6414: -- Standard call to get message count and if count is 1, get message info.
6415: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_msg_data);
6416: x_msg_data := SUBSTR(l_msg_data, 0, 198);
6417: EXCEPTION
6418: WHEN OTHERS THEN
6419: ROLLBACK TO apiinsertserial_apipub;
6421: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
6422: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertRangeUnitTrx');
6423: END IF;
6424:
6425: x_return_status := fnd_api.g_ret_sts_unexp_error;
6426: END insertRangeUnitTrx;
6427:
6428: --serial tagging
6429: -- Output variables
6447: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
6448: BEGIN
6449:
6450: x_serial_control := 1;
6451: x_return_status := fnd_api.g_ret_sts_success;
6452: IF p_txn_type_id = 1005 THEN
6453: l_txn_type_id := 36;
6454: ELSE
6455: l_txn_type_id := p_txn_type_id;
6567: END IF;
6568:
6569: END IF;
6570: x_serial_control := 1;
6571: x_return_status := fnd_api.g_ret_sts_unexp_error;
6572: END is_serial_controlled;
6573:
6574: --serial tagging
6575: FUNCTION is_serial_tagged(
6634: l_userid NUMBER := fnd_global.user_id;
6635: l_loginid NUMBER := fnd_global.login_id;
6636: BEGIN
6637:
6638: x_return_status := fnd_api.g_ret_sts_success;
6639:
6640: IF ( ( p_to_item_id IS NULL
6641: AND p_to_template_id IS NOT NULL)
6642: OR
6646: )
6647: THEN
6648: NULL;
6649: ELSE
6650: x_return_status := fnd_api.g_ret_sts_error;
6651: IF l_debug = 1 THEN
6652: invtrace('Incorrect Destination org/item/template Combination :' ||
6653: p_to_org_id || ':' || p_to_item_id || ':' || p_to_template_id );
6654: END IF;
6664: )
6665: THEN
6666: NULL;
6667: ELSE
6668: x_return_status := fnd_api.g_ret_sts_error;
6669: IF l_debug = 1 THEN
6670: invtrace('Incorrect Source org/item/template Combination :' ||
6671: p_from_org_id || ':' || p_from_item_id || ':' || p_from_template_id );
6672: END IF;
6692: ;
6693:
6694: EXCEPTION
6695: WHEN others THEN
6696: x_return_status := fnd_api.g_ret_sts_error;
6697: IF l_debug = 1 THEN
6698: invtrace('Exception in copyTagAssignments 1:'||SQLERRM);
6699: END IF;
6700: END;
6717: ;
6718:
6719: EXCEPTION
6720: WHEN others THEN
6721: x_return_status := fnd_api.g_ret_sts_error;
6722: IF l_debug = 1 THEN
6723: invtrace('Exception in copyTagAssignments 2:'||SQLERRM);
6724: END IF;
6725: END;
6742: ;
6743:
6744: EXCEPTION
6745: WHEN others THEN
6746: x_return_status := fnd_api.g_ret_sts_error;
6747: IF l_debug = 1 THEN
6748: invtrace('Exception in copyTagAssignments 3:'||SQLERRM);
6749: END IF;
6750: END;
6767: ;
6768:
6769: EXCEPTION
6770: WHEN others THEN
6771: x_return_status := fnd_api.g_ret_sts_error;
6772: IF l_debug = 1 THEN
6773: invtrace('Exception in copyTagAssignments 4:'||SQLERRM);
6774: END IF;
6775: END;
6778:
6779: EXCEPTION
6780: WHEN OTHERS THEN
6781:
6782: x_return_status := fnd_api.g_ret_sts_unexp_error;
6783: IF l_debug = 1 THEN
6784: invtrace('Exception in copyTagAssignments 4:'||SQLERRM);
6785: END IF;
6786: END copy_serial_tag_assignments;
6799: p_template_id IN NUMBER,
6800: x_return_status OUT NOCOPY VARCHAR2) IS
6801: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
6802: BEGIN
6803: x_return_status := fnd_api.g_ret_sts_success;
6804:
6805: IF ( p_inventory_item_id IS NULL
6806: AND p_template_id IS NOT NULL)
6807: THEN
6816: DELETE FROM MTL_SERIAL_TAGGING_ASSIGNMENTS
6817: WHERE inventory_item_id = p_inventory_item_id
6818: AND organization_id = p_organization_id;
6819: ELSE
6820: x_return_status := fnd_api.g_ret_sts_error;
6821: IF l_debug = 1 THEN
6822: invtrace('Incorrect org/item/template Combination :' ||
6823: p_organization_id || ':' || p_inventory_item_id || ':' || p_template_id );
6824: END IF;
6825: END IF;
6826:
6827: EXCEPTION
6828: WHEN OTHERS THEN
6829: x_return_status := fnd_api.g_ret_sts_unexp_error;
6830: IF l_debug = 1 THEN
6831: invtrace('Exception in delete_serial_tag_assignments:' || sqlerrm );
6832: END IF;
6833: