[Home] [Help]
1: PACKAGE BODY inv_serial_number_pub AS
2: /* $Header: INVPSNB.pls 120.21.12020000.4 2013/01/02 11:36:52 raitha ship $*/
3:
4: -- Global constant holding the package name
5: g_pkg_name CONSTANT VARCHAR2(30) := 'INV_SERIAL_NUMBER_PUB';
1: PACKAGE BODY inv_serial_number_pub AS
2: /* $Header: INVPSNB.pls 120.21.12020000.4 2013/01/02 11:36:52 raitha ship $*/
3:
4: -- Global constant holding the package name
5: g_pkg_name CONSTANT VARCHAR2(30) := 'INV_SERIAL_NUMBER_PUB';
6: /* -- Added for DMV Project */
7: -- Bug# 6825191, Commenting the variables to be obsoleted
8: --G_first_row_of_trx boolean := true;
9: --G_first_row_trx_tmp_id number := 0;
218: item_count := 0;
219: WHEN OTHERS THEN
220: --Bug 3153585:Raising exception to populate error message correctly.
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;
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');
271: END IF;
272:
273: RAISE fnd_api.g_exc_unexpected_error;
274: END;
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
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);
479: -- Standard Start of API savepoint
480: SAVEPOINT apiinsertserial_apipub;
481:
482: IF (l_debug = 1) THEN
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
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);
1364: WHEN NO_DATA_FOUND THEN
1365: item_count := 0;
1366: WHEN OTHERS THEN
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;
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;
1414:
1415: RAISE fnd_api.g_exc_unexpected_error;
1416: END;
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;
1441: RAISE fnd_api.g_exc_unexpected_error;
1442: END;
1443: --bug10074736,we should make sure the validate lot number here.
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;
1473: RAISE fnd_api.g_exc_unexpected_error;
1474: END;
1475: --bug10074682,validate the from serial number and to serial number available.
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);
1642: l_api_name CONSTANT VARCHAR2(30) := 'insert_range_serial';
1643: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1644: l_previous_status NUMBER; -- 13564609
1645: BEGIN
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
1693: WHEN OTHERS THEN
1694: NULL;
1695: END;
1696:
1697: --invtrace('INV_SERIAL_NUMBER_PUB', ' l_cur_serial_number, l_current_status AND l_group_mark_id ' || l_cur_serial_number || ' ' || l_current_status
1698: -- || ' : ' || l_group_mark_id);
1699:
1700: /* Bug #1767236
1701: * When the status is 4 (out of stores) then update the status do not insert
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
1756: inv_serial_number_pub.insertserial(
1757: p_api_version => p_api_version
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
1756: inv_serial_number_pub.insertserial(
1757: p_api_version => p_api_version
1758: , p_init_msg_list => p_init_msg_list
1759: , p_commit => p_commit
1760: , p_validation_level => p_validation_level
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;
3325: WHEN OTHERS THEN
3326: ROLLBACK TO apiinsertserial_apipub;
3327:
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;
4383: AND Length(p_f_ser_num) = Length(Nvl(p_t_ser_num,p_f_ser_num)) -- Added as part of the bug 14736743
4384: AND decode( msn.CURRENT_STATUS, 6, 1, msn.CURRENT_STATUS ) = l_status_after_p1 -- l_last_status -- p_last_status
4385: AND Nvl(msn.owning_tp_type,2) <> 1
4386: AND Nvl(msn.owning_organization_id,msn.current_organization_id) = msn.current_organization_id
4387: AND inv_serial_number_pub.valsn(
4388: p_trxsrctypid, -- trx_src_typ_id IN NUMBER,
4389: p_trx_act_id, -- trx_action_id IN NUMBER,
4390: p_rev, -- revision IN VARCHAR2,
4391: p_subinv, -- curr_subinv_code IN VARCHAR2,
4455: AND Length(p_f_ser_num) = Length(Nvl(p_t_ser_num,p_f_ser_num)) -- Added as part of the bug 14736743
4456: AND decode( msn.CURRENT_STATUS, 6, 1, msn.CURRENT_STATUS ) = l_last_status
4457: AND Nvl(msn.owning_organization_id,msn.current_organization_id) = msn.current_organization_id
4458: AND Nvl(msn.owning_tp_type,2) <> 1
4459: AND inv_serial_number_pub.valsn(
4460: p_trxsrctypid, -- trx_src_typ_id IN NUMBER,
4461: p_trx_act_id, -- trx_action_id IN NUMBER,
4462: p_rev, -- revision IN VARCHAR2,
4463: p_subinv, -- curr_subinv_code IN VARCHAR2,
6418: WHEN OTHERS THEN
6419: ROLLBACK TO apiinsertserial_apipub;
6420:
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;
6560:
6561: EXCEPTION
6562: WHEN OTHERS THEN
6563: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
6564: fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'is_serial_controlled');
6565: IF l_debug = 1 THEN
6566: invtrace('Exception:'||SQLERRM);
6567: END IF;
6568:
6832: END IF;
6833:
6834: END delete_serial_tag_assignments;
6835:
6836: END inv_serial_number_pub;