[Home] [Help]
397: -- --
398: -- Output Parameters --
399: -- x_return_status --
400: -- standard output parameter. Possible values are --
401: -- 1. fnd_api.g_ret_sts_success for success --
402: -- 2. fnd_api.g_ret_sts_exc_error for expected error --
403: -- 3. fnd_api.g_ret_sts_unexp_error for unexpected error --
404: -------------------------------------------------------------------------------
405: PROCEDURE build_sql
398: -- Output Parameters --
399: -- x_return_status --
400: -- standard output parameter. Possible values are --
401: -- 1. fnd_api.g_ret_sts_success for success --
402: -- 2. fnd_api.g_ret_sts_exc_error for expected error --
403: -- 3. fnd_api.g_ret_sts_unexp_error for unexpected error --
404: -------------------------------------------------------------------------------
405: PROCEDURE build_sql
406: (
399: -- x_return_status --
400: -- standard output parameter. Possible values are --
401: -- 1. fnd_api.g_ret_sts_success for success --
402: -- 2. fnd_api.g_ret_sts_exc_error for expected error --
403: -- 3. fnd_api.g_ret_sts_unexp_error for unexpected error --
404: -------------------------------------------------------------------------------
405: PROCEDURE build_sql
406: (
407: x_return_status OUT NOCOPY VARCHAR2
414: , p_pick_release IN NUMBER
415: , x_sql_statement OUT NOCOPY long
416: , p_is_revision_control IN BOOLEAN
417: ) IS
418: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
419: --
420: l_stmt long;
421: l_asset_sub_where long;
422: l_revision_select long;
1011: x_sql_statement := l_stmt;
1012:
1013: EXCEPTION
1014: WHEN OTHERS THEN
1015: x_return_status := fnd_api.g_ret_sts_unexp_error;
1016: END build_sql;
1017: -- End of build_sql .. NO invConv change.
1018:
1019: procedure showsql( p_sql_text IN long )
1064: , p_pick_release IN NUMBER
1065: , x_cursor OUT NOCOPY NUMBER
1066: , p_is_revision_control IN BOOLEAN DEFAULT TRUE
1067: ) IS
1068: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1069: l_cursor NUMBER;
1070: l_sql LONG;
1071: l_last_error_pos NUMBER;
1072: l_temp_str VARCHAR2(30);
1090: p_lot_expiration_date,p_onhand_source,
1091: p_pick_release,
1092: l_sql, p_is_revision_control);
1093:
1094: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1095: RAISE fnd_api.g_exc_unexpected_error;
1096: END IF;
1097:
1098: -- print the sql statement to dbms_output
1091: p_pick_release,
1092: l_sql, p_is_revision_control);
1093:
1094: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1095: RAISE fnd_api.g_exc_unexpected_error;
1096: END IF;
1097:
1098: -- print the sql statement to dbms_output
1099: --dbms_output.put_line('start build sql');
1156: -- dbms_output.put_line(l_err);
1157: -- dbms_output.put_line(Sqlerrm);
1158: -- dbms_output.put_line('start');
1159: -- inv_ppcommon_pvt.showsql(l_sql);
1160: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1161: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1162: THEN
1163: fnd_msg_pub.add_exc_msg
1164: ( g_pkg_name
1257: , p_pick_release => 0
1258: , x_cursor => l_cursor
1259: );
1260:
1261: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1262: RAISE fnd_api.g_exc_unexpected_error;
1263: END IF;
1264:
1265: dbms_sql.define_column(l_cursor,1,l_organization_id);
1258: , x_cursor => l_cursor
1259: );
1260:
1261: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1262: RAISE fnd_api.g_exc_unexpected_error;
1263: END IF;
1264:
1265: dbms_sql.define_column(l_cursor,1,l_organization_id);
1266: dbms_sql.define_column(l_cursor,2,l_inventory_item_id);
1400: l_node_index INTEGER;
1401:
1402: l_found BOOLEAN;
1403: l_root_id NUMBER;
1404: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1405: l_reservable VARCHAR2(10);
1406: b_reservable BOOLEAN;
1407: l_status_id NUMBER;
1408: l_zone_control NUMBER;
1543: PROCEDURE zero_tree_node
1544: (x_return_status OUT NOCOPY VARCHAR2
1545: , p_node_index IN INTEGER
1546: ) IS
1547: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1548: l_node_index INTEGER;
1549: BEGIN
1550: l_node_index := p_node_index;
1551: IF l_node_index <>0 THEN
1569: l_node_index := g_nodes(l_node_index).first_child_index;
1570: WHILE l_node_index<>0 LOOP
1571: zero_tree_node(l_return_status, l_node_index);
1572:
1573: IF l_return_status = fnd_api.g_ret_sts_error THEN
1574: RAISE fnd_api.g_exc_error;
1575: End IF ;
1576:
1577: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1570: WHILE l_node_index<>0 LOOP
1571: zero_tree_node(l_return_status, l_node_index);
1572:
1573: IF l_return_status = fnd_api.g_ret_sts_error THEN
1574: RAISE fnd_api.g_exc_error;
1575: End IF ;
1576:
1577: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1578: RAISE fnd_api.g_exc_unexpected_error;
1573: IF l_return_status = fnd_api.g_ret_sts_error THEN
1574: RAISE fnd_api.g_exc_error;
1575: End IF ;
1576:
1577: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1578: RAISE fnd_api.g_exc_unexpected_error;
1579: End IF;
1580:
1581: l_node_index := g_nodes(l_node_index).next_sibling_index;
1574: RAISE fnd_api.g_exc_error;
1575: End IF ;
1576:
1577: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1578: RAISE fnd_api.g_exc_unexpected_error;
1579: End IF;
1580:
1581: l_node_index := g_nodes(l_node_index).next_sibling_index;
1582: END LOOP;
1583: END IF;
1584: x_return_status := l_return_status;
1585:
1586: EXCEPTION
1587: WHEN fnd_api.g_exc_error THEN
1588: x_return_status := fnd_api.g_ret_sts_error;
1589:
1590: WHEN fnd_api.g_exc_unexpected_error THEN
1591: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1584: x_return_status := l_return_status;
1585:
1586: EXCEPTION
1587: WHEN fnd_api.g_exc_error THEN
1588: x_return_status := fnd_api.g_ret_sts_error;
1589:
1590: WHEN fnd_api.g_exc_unexpected_error THEN
1591: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1592:
1586: EXCEPTION
1587: WHEN fnd_api.g_exc_error THEN
1588: x_return_status := fnd_api.g_ret_sts_error;
1589:
1590: WHEN fnd_api.g_exc_unexpected_error THEN
1591: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1592:
1593: WHEN OTHERS THEN
1594: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1587: WHEN fnd_api.g_exc_error THEN
1588: x_return_status := fnd_api.g_ret_sts_error;
1589:
1590: WHEN fnd_api.g_exc_unexpected_error THEN
1591: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1592:
1593: WHEN OTHERS THEN
1594: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1595:
1590: WHEN fnd_api.g_exc_unexpected_error THEN
1591: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1592:
1593: WHEN OTHERS THEN
1594: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1595:
1596: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1597: THEN
1598: fnd_msg_pub.add_exc_msg
1606: PROCEDURE zero_tree
1607: (x_return_status OUT NOCOPY VARCHAR2
1608: , p_tree_id IN INTEGER
1609: ) IS
1610: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1611: l_node_index INTEGER;
1612: BEGIN
1613: l_node_index := g_rootinfos(p_tree_id).item_node_index;
1614:
1613: l_node_index := g_rootinfos(p_tree_id).item_node_index;
1614:
1615: zero_tree_node(l_return_status, l_node_index);
1616:
1617: IF l_return_status = fnd_api.g_ret_sts_error THEN
1618: RAISE fnd_api.g_exc_error;
1619: End IF ;
1620:
1621: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1614:
1615: zero_tree_node(l_return_status, l_node_index);
1616:
1617: IF l_return_status = fnd_api.g_ret_sts_error THEN
1618: RAISE fnd_api.g_exc_error;
1619: End IF ;
1620:
1621: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1622: RAISE fnd_api.g_exc_unexpected_error;
1617: IF l_return_status = fnd_api.g_ret_sts_error THEN
1618: RAISE fnd_api.g_exc_error;
1619: End IF ;
1620:
1621: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1622: RAISE fnd_api.g_exc_unexpected_error;
1623: End IF;
1624:
1625: x_return_status := l_return_status;
1618: RAISE fnd_api.g_exc_error;
1619: End IF ;
1620:
1621: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1622: RAISE fnd_api.g_exc_unexpected_error;
1623: End IF;
1624:
1625: x_return_status := l_return_status;
1626:
1624:
1625: x_return_status := l_return_status;
1626:
1627: EXCEPTION
1628: WHEN fnd_api.g_exc_error THEN
1629: x_return_status := fnd_api.g_ret_sts_error;
1630:
1631: WHEN fnd_api.g_exc_unexpected_error THEN
1632: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1625: x_return_status := l_return_status;
1626:
1627: EXCEPTION
1628: WHEN fnd_api.g_exc_error THEN
1629: x_return_status := fnd_api.g_ret_sts_error;
1630:
1631: WHEN fnd_api.g_exc_unexpected_error THEN
1632: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1633:
1627: EXCEPTION
1628: WHEN fnd_api.g_exc_error THEN
1629: x_return_status := fnd_api.g_ret_sts_error;
1630:
1631: WHEN fnd_api.g_exc_unexpected_error THEN
1632: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1633:
1634: WHEN OTHERS THEN
1635: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1628: WHEN fnd_api.g_exc_error THEN
1629: x_return_status := fnd_api.g_ret_sts_error;
1630:
1631: WHEN fnd_api.g_exc_unexpected_error THEN
1632: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1633:
1634: WHEN OTHERS THEN
1635: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1636:
1631: WHEN fnd_api.g_exc_unexpected_error THEN
1632: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1633:
1634: WHEN OTHERS THEN
1635: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1636:
1637: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1638: THEN
1639: fnd_msg_pub.add_exc_msg
1663: , p_cost_group_id IN NUMBER DEFAULT NULL
1664: , p_node_index IN INTEGER
1665: , p_hash_string IN VARCHAR2 DEFAULT NULL
1666: ) IS
1667: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1668: l_new_node_index INTEGER;
1669: l_org_id NUMBER;
1670: l_parent_index INTEGER;
1671: l_last_child_index INTEGER;
1715:
1716: EXCEPTION
1717:
1718: WHEN OTHERS THEN
1719: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1720:
1721: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1722: THEN
1723: fnd_msg_pub.add_exc_msg
1749: l_pjm_enabled VARCHAR2(1);
1750:
1751: BEGIN
1752:
1753: x_return_status := fnd_api.g_ret_sts_success;
1754:
1755: -- Bug 1918356
1756: -- If PJM unit effectivity is enabled, we need to create
1757: -- a new tree for each different sales order line id
1802:
1803: RETURN l_hash_string;
1804: EXCEPTION
1805: WHEN OTHERS THEN
1806: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1807:
1808: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1809: THEN
1810: fnd_msg_pub.add_exc_msg
1841: , p_pick_release IN NUMBER
1842: , p_grade_code IN VARCHAR2 DEFAULT NULL -- invConv change
1843: , x_tree_id OUT NOCOPY INTEGER
1844: ) IS
1845: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1846: l_item_node_index INTEGER;
1847: l_negative_inv_receipt_code NUMBER;
1848: l_disable_flag NUMBER;
1849:
1908: l_tree_index := 1;
1909: END IF;
1910: -- if this ever true, we've used all the possible indices. Raise an exception.
1911: IF l_tree_index = l_original_tree_index THEN
1912: RAISE fnd_api.g_exc_unexpected_error;
1913: END IF;
1914: END LOOP;
1915: END IF;
1916:
1985: , p_node_level => g_item_level
1986: , p_node_index => l_item_node_index
1987: );
1988:
1989: IF l_return_status = fnd_api.g_ret_sts_error THEN
1990: RAISE fnd_api.g_exc_error;
1991: End IF ;
1992:
1993: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1986: , p_node_index => l_item_node_index
1987: );
1988:
1989: IF l_return_status = fnd_api.g_ret_sts_error THEN
1990: RAISE fnd_api.g_exc_error;
1991: End IF ;
1992:
1993: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1994: RAISE fnd_api.g_exc_unexpected_error;
1989: IF l_return_status = fnd_api.g_ret_sts_error THEN
1990: RAISE fnd_api.g_exc_error;
1991: End IF ;
1992:
1993: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1994: RAISE fnd_api.g_exc_unexpected_error;
1995: End IF;
1996:
1997: g_rootinfos(l_tree_index).item_node_index := l_item_node_index;
1990: RAISE fnd_api.g_exc_error;
1991: End IF ;
1992:
1993: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1994: RAISE fnd_api.g_exc_unexpected_error;
1995: End IF;
1996:
1997: g_rootinfos(l_tree_index).item_node_index := l_item_node_index;
1998:
1997: g_rootinfos(l_tree_index).item_node_index := l_item_node_index;
1998:
1999: l_return_value := INV_CACHE.set_org_rec(p_organization_id);
2000: IF NOT l_return_value THEN
2001: RAISE fnd_api.g_exc_unexpected_error;
2002: END IF;
2003: l_negative_inv_receipt_code := INV_CACHE.org_rec.negative_inv_receipt_code;
2004:
2005: IF (l_negative_inv_receipt_code = 1) THEN
2016: ,x_organization_id => p_organization_id);
2017: */
2018: l_return_value := INV_CACHE.set_item_rec(p_organization_id, p_inventory_item_id);
2019: IF NOT l_return_value THEN
2020: RAISE fnd_api.g_exc_unexpected_error;
2021: END IF;
2022: l_unit_effective := INV_CACHE.item_rec.effectivity_control;
2023:
2024: /*Bug#5352407. All EAM asset group items are unit effective. Though they are unit
2062: print_debug('in new_tree org_id ='||g_rootinfos(l_tree_index).organization_id);
2063:
2064: EXCEPTION
2065:
2066: WHEN fnd_api.g_exc_error THEN
2067: x_return_status := fnd_api.g_ret_sts_error;
2068:
2069: WHEN fnd_api.g_exc_unexpected_error THEN
2070: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2063:
2064: EXCEPTION
2065:
2066: WHEN fnd_api.g_exc_error THEN
2067: x_return_status := fnd_api.g_ret_sts_error;
2068:
2069: WHEN fnd_api.g_exc_unexpected_error THEN
2070: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2071:
2065:
2066: WHEN fnd_api.g_exc_error THEN
2067: x_return_status := fnd_api.g_ret_sts_error;
2068:
2069: WHEN fnd_api.g_exc_unexpected_error THEN
2070: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2071:
2072: WHEN OTHERS THEN
2073: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2066: WHEN fnd_api.g_exc_error THEN
2067: x_return_status := fnd_api.g_ret_sts_error;
2068:
2069: WHEN fnd_api.g_exc_unexpected_error THEN
2070: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2071:
2072: WHEN OTHERS THEN
2073: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2074:
2069: WHEN fnd_api.g_exc_unexpected_error THEN
2070: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2071:
2072: WHEN OTHERS THEN
2073: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2074:
2075: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2076: THEN
2077: fnd_msg_pub.add_exc_msg
2126:
2127: IS
2128: l_api_version_number CONSTANT NUMBER := 1.0;
2129: l_api_name CONSTANT VARCHAR2(30) := 'Lock_Tree';
2130: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2131: l_lock_handle VARCHAR2(128);
2132: l_status INTEGER;
2133:
2134: BEGIN
2133:
2134: BEGIN
2135: print_debug('... in lock_tree... will create a dbms_lock...');
2136: -- Standard call to check for call compatibility
2137: IF NOT fnd_api.compatible_api_call( l_api_version_number
2138: , p_api_version_number
2139: , l_api_name
2140: , G_PKG_NAME
2141: ) THEN
2138: , p_api_version_number
2139: , l_api_name
2140: , G_PKG_NAME
2141: ) THEN
2142: RAISE fnd_api.g_exc_unexpected_error;
2143: END IF;
2144:
2145: -- Initialize message list.
2146: IF fnd_api.to_boolean(p_init_msg_lst) THEN
2142: RAISE fnd_api.g_exc_unexpected_error;
2143: END IF;
2144:
2145: -- Initialize message list.
2146: IF fnd_api.to_boolean(p_init_msg_lst) THEN
2147: fnd_msg_pub.initialize;
2148: END IF;
2149:
2150: --validate organization and inventory item
2151: IF (p_organization_id IS NULL OR p_inventory_item_id IS NULL) THEN
2152: --raise error condition
2153: fnd_message.set_name('INV','INV_LOCK_MISSING_ARGS');
2154: fnd_msg_pub.ADD;
2155: RAISE fnd_api.g_exc_error;
2156: END IF;
2157:
2158: IF g_lock_timeout IS NULL THEN
2159: g_lock_timeout := nvl(to_number(fnd_profile.value('INV_QTY_TREE_TIMEOUT')),dbms_lock.maxwait);
2174: IF (l_status NOT IN (0,4)) THEN
2175: if (l_status = 1) then -- timeout
2176: fnd_message.set_name('INV','INV_LOCK_TREE_TIMEOUT');
2177: fnd_msg_pub.ADD;
2178: RAISE fnd_api.g_exc_error;
2179: elsif (l_status = 2) then -- deadlock
2180: fnd_message.set_name('INV','INV_LOCK_TREE_DEADLOCK');
2181: fnd_msg_pub.ADD;
2182: RAISE fnd_api.g_exc_error;
2178: RAISE fnd_api.g_exc_error;
2179: elsif (l_status = 2) then -- deadlock
2180: fnd_message.set_name('INV','INV_LOCK_TREE_DEADLOCK');
2181: fnd_msg_pub.ADD;
2182: RAISE fnd_api.g_exc_error;
2183: else -- internal error - not fault of user
2184: fnd_message.set_name('INV','INV_LOCK_TREE_ERROR');
2185: fnd_msg_pub.ADD;
2186: RAISE fnd_api.g_exc_error;
2182: RAISE fnd_api.g_exc_error;
2183: else -- internal error - not fault of user
2184: fnd_message.set_name('INV','INV_LOCK_TREE_ERROR');
2185: fnd_msg_pub.ADD;
2186: RAISE fnd_api.g_exc_error;
2187: end if;
2188: END IF;
2189:
2190: print_debug('QTY TREE LOCK ACQUIRED '||l_lock_handle, 9);
2192: x_return_status := l_return_status;
2193:
2194: EXCEPTION
2195:
2196: WHEN fnd_api.g_exc_error THEN
2197: x_return_status := fnd_api.g_ret_sts_error;
2198:
2199: WHEN fnd_api.g_exc_unexpected_error THEN
2200: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2193:
2194: EXCEPTION
2195:
2196: WHEN fnd_api.g_exc_error THEN
2197: x_return_status := fnd_api.g_ret_sts_error;
2198:
2199: WHEN fnd_api.g_exc_unexpected_error THEN
2200: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2201:
2195:
2196: WHEN fnd_api.g_exc_error THEN
2197: x_return_status := fnd_api.g_ret_sts_error;
2198:
2199: WHEN fnd_api.g_exc_unexpected_error THEN
2200: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2201:
2202: WHEN OTHERS THEN
2203: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2196: WHEN fnd_api.g_exc_error THEN
2197: x_return_status := fnd_api.g_ret_sts_error;
2198:
2199: WHEN fnd_api.g_exc_unexpected_error THEN
2200: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2201:
2202: WHEN OTHERS THEN
2203: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2204:
2199: WHEN fnd_api.g_exc_unexpected_error THEN
2200: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2201:
2202: WHEN OTHERS THEN
2203: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2204:
2205: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2206: THEN
2207: fnd_msg_pub.add_exc_msg
2231:
2232: IS
2233: l_api_version_number CONSTANT NUMBER := 1.0;
2234: l_api_name CONSTANT VARCHAR2(30) := 'Release_Lock';
2235: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2236: l_lock_handle VARCHAR2(128);
2237: l_status INTEGER;
2238: BEGIN
2239: print_debug('... in release_lock ...');
2237: l_status INTEGER;
2238: BEGIN
2239: print_debug('... in release_lock ...');
2240: -- Standard call to check for call compatibility
2241: IF NOT fnd_api.compatible_api_call(l_api_version_number
2242: , p_api_version_number
2243: , l_api_name
2244: , G_PKG_NAME
2245: ) THEN
2242: , p_api_version_number
2243: , l_api_name
2244: , G_PKG_NAME
2245: ) THEN
2246: RAISE fnd_api.g_exc_unexpected_error;
2247: END IF;
2248:
2249: -- Initialize message list.
2250: IF fnd_api.to_boolean(p_init_msg_lst) THEN
2246: RAISE fnd_api.g_exc_unexpected_error;
2247: END IF;
2248:
2249: -- Initialize message list.
2250: IF fnd_api.to_boolean(p_init_msg_lst) THEN
2251: fnd_msg_pub.initialize;
2252: END IF;
2253:
2254: --validate organization and inventory item
2256: p_inventory_item_id IS NULL) THEN
2257: --raise error condition
2258: fnd_message.set_name('INV','INV_LOCK_RELEASE_MISSING_ARGS');
2259: fnd_msg_pub.ADD;
2260: RAISE fnd_api.g_exc_error;
2261: END IF;
2262:
2263:
2264: --get lock handle by calling helper function
2273: --if parameter error or illegal lock handle (internal error)
2274: if l_status IN (3,5) THEN
2275: fnd_message.set_name('INV','INV_LOCK_RELEASE_ERROR');
2276: fnd_msg_pub.ADD;
2277: RAISE fnd_api.g_exc_error;
2278: end if;
2279:
2280: print_debug('... Normal end of release_lock ...');
2281: x_return_status := l_return_status;
2281: x_return_status := l_return_status;
2282:
2283: EXCEPTION
2284:
2285: WHEN fnd_api.g_exc_error THEN
2286: print_debug('... release_lock EXP_ERROR='||SQLERRM, 9);
2287: x_return_status := fnd_api.g_ret_sts_error;
2288:
2289: WHEN fnd_api.g_exc_unexpected_error THEN
2283: EXCEPTION
2284:
2285: WHEN fnd_api.g_exc_error THEN
2286: print_debug('... release_lock EXP_ERROR='||SQLERRM, 9);
2287: x_return_status := fnd_api.g_ret_sts_error;
2288:
2289: WHEN fnd_api.g_exc_unexpected_error THEN
2290: print_debug('... release_lock UNEXP_ERROR='||SQLERRM, 9);
2291: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2285: WHEN fnd_api.g_exc_error THEN
2286: print_debug('... release_lock EXP_ERROR='||SQLERRM, 9);
2287: x_return_status := fnd_api.g_ret_sts_error;
2288:
2289: WHEN fnd_api.g_exc_unexpected_error THEN
2290: print_debug('... release_lock UNEXP_ERROR='||SQLERRM, 9);
2291: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2292:
2293: WHEN OTHERS THEN
2287: x_return_status := fnd_api.g_ret_sts_error;
2288:
2289: WHEN fnd_api.g_exc_unexpected_error THEN
2290: print_debug('... release_lock UNEXP_ERROR='||SQLERRM, 9);
2291: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2292:
2293: WHEN OTHERS THEN
2294: print_debug('... release_lock others='||SQLERRM, 9);
2295: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2291: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2292:
2293: WHEN OTHERS THEN
2294: print_debug('... release_lock others='||SQLERRM, 9);
2295: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2296:
2297: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2298: THEN
2299: fnd_msg_pub.add_exc_msg
2357: , p_organization_id IN VARCHAR2
2358: , p_subinventory_code IN VARCHAR2
2359: , x_is_reservable_sub OUT NOCOPY BOOLEAN
2360: ) IS
2361: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2362: l_reservable_type NUMBER := 2;
2363: l_hash_value NUMBER;
2364: BEGIN
2365: --Bug 4699159. The query is throwing a no data found exception when
2395:
2396: EXCEPTION
2397:
2398: WHEN OTHERS THEN
2399: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2400:
2401: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2402: THEN
2403: fnd_msg_pub.add_exc_msg
2419: , p_organization_id IN NUMBER
2420: , p_inventory_item_id IN NUMBER
2421: , x_is_reservable_item OUT NOCOPY BOOLEAN
2422: ) IS
2423: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2424: l_reservable_type NUMBER;
2425: l_no_info BOOLEAN := FALSE;
2426: BEGIN
2427: IF NOT g_is_item_reservable.exists(p_inventory_item_id) THEN
2451:
2452: EXCEPTION
2453:
2454: WHEN OTHERS THEN
2455: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2456:
2457: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2458: THEN
2459: fnd_msg_pub.add_exc_msg
2482: , x_is_reservable OUT NOCOPY BOOLEAN
2483: , p_lpn_id IN NUMBER DEFAULT NULL -- Onhand Material Status Support
2484: ) IS
2485:
2486: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2487: l_reservable_type NUMBER;
2488: l_default_onhand_status_id NUMBER; -- Onhand Material Status Support
2489:
2490: /* Bug 6719389 */
2784: EXCEPTION
2785:
2786: WHEN OTHERS THEN
2787: print_debug('in check_is_reservable, OTHERS Error='||SQLERRM, 9);
2788: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2789:
2790: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2791: THEN
2792: fnd_msg_pub.add_exc_msg
2893: , p_pick_release IN NUMBER
2894: ) RETURN INTEGER
2895: IS
2896:
2897: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2898: l_rootinfo_index INTEGER;
2899: l_hash_base NUMBER;
2900: l_hash_size NUMBER;
2901: l_hash_string VARCHAR2(1000);
2979: End If;
2980: -- if this ever true, we've used all the possible indices. Raise
2981: -- an exception.
2982: If l_tree_index = l_original_tree_index Then
2983: RAISE fnd_api.g_exc_unexpected_error;
2984: End If;
2985: END LOOP;
2986: --Bug 5241485 fix. This one causing no data found exception
2987: print_debug('in find_rootinfo: AFTER LOOP l_tree_index:'||l_tree_index);
3088: END IF;
3089:
3090: EXCEPTION
3091: WHEN OTHERS THEN
3092: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3093: print_debug('odab in find_rootinfo OTHERS='||SQLERRM, 9);
3094: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3095: THEN
3096: fnd_msg_pub.add_exc_msg
3121: , p_lpn_id IN NUMBER DEFAULT NULL
3122: , p_cost_group_id IN NUMBER DEFAULT NULL
3123: , x_child_index OUT NOCOPY INTEGER
3124: ) RETURN BOOLEAN IS
3125: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3126: l_node_index INTEGER;
3127: l_hash_string VARCHAR2(300);
3128: l_hash_size NUMBER;
3129: l_hash_base NUMBER := 1;
3139: THEN
3140: fnd_message.set_name('INV','INV-Cannot find node');
3141: fnd_message.set_token('ROUTINE', 'Find_Child_Node');
3142: fnd_msg_pub.ADD;
3143: RAISE fnd_api.g_exc_error;
3144: END IF;
3145:
3146:
3147: print_debug('... in find_child_node, building the hash string...');
3199: RETURN TRUE;
3200:
3201: EXCEPTION
3202:
3203: WHEN fnd_api.g_exc_error THEN
3204: x_return_status := fnd_api.g_ret_sts_error;
3205:
3206: WHEN fnd_api.g_exc_unexpected_error THEN
3207: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3200:
3201: EXCEPTION
3202:
3203: WHEN fnd_api.g_exc_error THEN
3204: x_return_status := fnd_api.g_ret_sts_error;
3205:
3206: WHEN fnd_api.g_exc_unexpected_error THEN
3207: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3208:
3202:
3203: WHEN fnd_api.g_exc_error THEN
3204: x_return_status := fnd_api.g_ret_sts_error;
3205:
3206: WHEN fnd_api.g_exc_unexpected_error THEN
3207: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3208:
3209: WHEN OTHERS THEN
3210: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3203: WHEN fnd_api.g_exc_error THEN
3204: x_return_status := fnd_api.g_ret_sts_error;
3205:
3206: WHEN fnd_api.g_exc_unexpected_error THEN
3207: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3208:
3209: WHEN OTHERS THEN
3210: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3211:
3206: WHEN fnd_api.g_exc_unexpected_error THEN
3207: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3208:
3209: WHEN OTHERS THEN
3210: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3211:
3212: -- For performance reasons during pick release only do this ifdebug is on
3213: IF ((NOT g_is_pickrelease) OR nvl(g_debug,2) = 1) THEN
3214: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3239: , x_node_index OUT NOCOPY INTEGER
3240: , p_lpn_id IN NUMBER
3241: , p_cost_group_id IN NUMBER
3242: ) RETURN BOOLEAN IS
3243: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3244: l_current_node_index INTEGER;
3245: l_child_node_index INTEGER;
3246: l_found BOOLEAN;
3247: l_last_child INTEGER;
3271: * , p_cost_group_id => p_cost_group_id
3272: * , x_child_index => l_current_node_index
3273: * );
3274: *
3275: * IF l_return_status = fnd_api.g_ret_sts_error THEN
3276: * RAISE fnd_api.g_exc_error;
3277: * End IF ;
3278: *
3279: * IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3272: * , x_child_index => l_current_node_index
3273: * );
3274: *
3275: * IF l_return_status = fnd_api.g_ret_sts_error THEN
3276: * RAISE fnd_api.g_exc_error;
3277: * End IF ;
3278: *
3279: * IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3280: * RAISE fnd_api.g_exc_unexpected_error;
3275: * IF l_return_status = fnd_api.g_ret_sts_error THEN
3276: * RAISE fnd_api.g_exc_error;
3277: * End IF ;
3278: *
3279: * IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3280: * RAISE fnd_api.g_exc_unexpected_error;
3281: * End IF;
3282: *
3283: * IF l_found = FALSE THEN
3276: * RAISE fnd_api.g_exc_error;
3277: * End IF ;
3278: *
3279: * IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3280: * RAISE fnd_api.g_exc_unexpected_error;
3281: * End IF;
3282: *
3283: * IF l_found = FALSE THEN
3284: * fnd_message.set_name('INV','INV-Cannot find node');
3283: * IF l_found = FALSE THEN
3284: * fnd_message.set_name('INV','INV-Cannot find node');
3285: * fnd_message.set_token('ROUTINE', 'Find_Tree_Node');
3286: * fnd_msg_pub.ADD;
3287: * RAISE fnd_api.g_exc_unexpected_error;
3288: * End IF;
3289: *
3290: * l_child_node_index := l_current_node_index;
3291: * x_node_index := l_current_node_index;
3311: , p_cost_group_id => NULL
3312: , x_child_index => l_current_node_index
3313: );
3314:
3315: IF l_return_status = fnd_api.g_ret_sts_error THEN
3316: RAISE fnd_api.g_exc_error;
3317: End IF ;
3318:
3319: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3312: , x_child_index => l_current_node_index
3313: );
3314:
3315: IF l_return_status = fnd_api.g_ret_sts_error THEN
3316: RAISE fnd_api.g_exc_error;
3317: End IF ;
3318:
3319: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3320: RAISE fnd_api.g_exc_unexpected_error;
3315: IF l_return_status = fnd_api.g_ret_sts_error THEN
3316: RAISE fnd_api.g_exc_error;
3317: End IF ;
3318:
3319: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3320: RAISE fnd_api.g_exc_unexpected_error;
3321: End IF;
3322:
3323: IF l_found = FALSE THEN
3316: RAISE fnd_api.g_exc_error;
3317: End IF ;
3318:
3319: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3320: RAISE fnd_api.g_exc_unexpected_error;
3321: End IF;
3322:
3323: IF l_found = FALSE THEN
3324: fnd_message.set_name('INV','INV-Cannot find node');
3323: IF l_found = FALSE THEN
3324: fnd_message.set_name('INV','INV-Cannot find node');
3325: fnd_message.set_token('ROUTINE', 'Find_Tree_Node');
3326: fnd_msg_pub.ADD;
3327: RAISE fnd_api.g_exc_unexpected_error;
3328: End IF;
3329:
3330: IF l_child_node_index <> 0 THEN
3331: IF g_nodes(l_current_node_index).first_child_index = 0 THEN
3365: , p_cost_group_id => NULL
3366: , x_child_index => l_current_node_index
3367: );
3368:
3369: IF l_return_status = fnd_api.g_ret_sts_error THEN
3370: RAISE fnd_api.g_exc_error;
3371: End IF ;
3372:
3373: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3366: , x_child_index => l_current_node_index
3367: );
3368:
3369: IF l_return_status = fnd_api.g_ret_sts_error THEN
3370: RAISE fnd_api.g_exc_error;
3371: End IF ;
3372:
3373: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3374: RAISE fnd_api.g_exc_unexpected_error;
3369: IF l_return_status = fnd_api.g_ret_sts_error THEN
3370: RAISE fnd_api.g_exc_error;
3371: End IF ;
3372:
3373: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3374: RAISE fnd_api.g_exc_unexpected_error;
3375: End IF;
3376:
3377: IF l_found = FALSE THEN
3370: RAISE fnd_api.g_exc_error;
3371: End IF ;
3372:
3373: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3374: RAISE fnd_api.g_exc_unexpected_error;
3375: End IF;
3376:
3377: IF l_found = FALSE THEN
3378: fnd_message.set_name('INV','INV-Cannot find node');
3377: IF l_found = FALSE THEN
3378: fnd_message.set_name('INV','INV-Cannot find node');
3379: fnd_message.set_token('ROUTINE', 'Find_Tree_Node');
3380: fnd_msg_pub.ADD;
3381: RAISE fnd_api.g_exc_unexpected_error;
3382: End IF;
3383:
3384: If l_child_node_index <> 0 Then
3385: if g_nodes(l_current_node_index).first_child_index = 0 then
3418: , p_cost_group_id => NULL
3419: , x_child_index => l_current_node_index
3420: );
3421:
3422: IF l_return_status = fnd_api.g_ret_sts_error THEN
3423: RAISE fnd_api.g_exc_error;
3424: End IF ;
3425:
3426: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3419: , x_child_index => l_current_node_index
3420: );
3421:
3422: IF l_return_status = fnd_api.g_ret_sts_error THEN
3423: RAISE fnd_api.g_exc_error;
3424: End IF ;
3425:
3426: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3427: RAISE fnd_api.g_exc_unexpected_error;
3422: IF l_return_status = fnd_api.g_ret_sts_error THEN
3423: RAISE fnd_api.g_exc_error;
3424: End IF ;
3425:
3426: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3427: RAISE fnd_api.g_exc_unexpected_error;
3428: End IF;
3429:
3430: IF l_found = FALSE THEN
3423: RAISE fnd_api.g_exc_error;
3424: End IF ;
3425:
3426: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3427: RAISE fnd_api.g_exc_unexpected_error;
3428: End IF;
3429:
3430: IF l_found = FALSE THEN
3431: fnd_message.set_name('INV','INV-Cannot find node');
3430: IF l_found = FALSE THEN
3431: fnd_message.set_name('INV','INV-Cannot find node');
3432: fnd_message.set_token('ROUTINE', 'Find_Tree_Node');
3433: fnd_msg_pub.ADD;
3434: RAISE fnd_api.g_exc_unexpected_error;
3435: End IF;
3436:
3437: If l_child_node_index <> 0 Then
3438: if g_nodes(l_current_node_index).first_child_index = 0 then
3471: , p_cost_group_id => NULL
3472: , x_child_index => l_current_node_index
3473: );
3474:
3475: IF l_return_status = fnd_api.g_ret_sts_error THEN
3476: RAISE fnd_api.g_exc_error;
3477: End IF ;
3478:
3479: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3472: , x_child_index => l_current_node_index
3473: );
3474:
3475: IF l_return_status = fnd_api.g_ret_sts_error THEN
3476: RAISE fnd_api.g_exc_error;
3477: End IF ;
3478:
3479: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3480: RAISE fnd_api.g_exc_unexpected_error;
3475: IF l_return_status = fnd_api.g_ret_sts_error THEN
3476: RAISE fnd_api.g_exc_error;
3477: End IF ;
3478:
3479: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3480: RAISE fnd_api.g_exc_unexpected_error;
3481: End IF;
3482:
3483: IF l_found = FALSE THEN
3476: RAISE fnd_api.g_exc_error;
3477: End IF ;
3478:
3479: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3480: RAISE fnd_api.g_exc_unexpected_error;
3481: End IF;
3482:
3483: IF l_found = FALSE THEN
3484: fnd_message.set_name('INV','INV-Cannot find node');
3483: IF l_found = FALSE THEN
3484: fnd_message.set_name('INV','INV-Cannot find node');
3485: fnd_message.set_token('ROUTINE', 'Find_Tree_Node');
3486: fnd_msg_pub.ADD;
3487: RAISE fnd_api.g_exc_unexpected_error;
3488: End IF;
3489:
3490: If l_child_node_index <> 0 Then
3491: if g_nodes(l_current_node_index).first_child_index = 0 then
3524: , p_cost_group_id => NULL
3525: , x_child_index => l_current_node_index
3526: );
3527:
3528: IF l_return_status = fnd_api.g_ret_sts_error THEN
3529: RAISE fnd_api.g_exc_error;
3530: End IF ;
3531:
3532: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3525: , x_child_index => l_current_node_index
3526: );
3527:
3528: IF l_return_status = fnd_api.g_ret_sts_error THEN
3529: RAISE fnd_api.g_exc_error;
3530: End IF ;
3531:
3532: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3533: RAISE fnd_api.g_exc_unexpected_error;
3528: IF l_return_status = fnd_api.g_ret_sts_error THEN
3529: RAISE fnd_api.g_exc_error;
3530: End IF ;
3531:
3532: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3533: RAISE fnd_api.g_exc_unexpected_error;
3534: End IF;
3535:
3536: IF l_found = FALSE THEN
3529: RAISE fnd_api.g_exc_error;
3530: End IF ;
3531:
3532: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3533: RAISE fnd_api.g_exc_unexpected_error;
3534: End IF;
3535:
3536: IF l_found = FALSE THEN
3537: fnd_message.set_name('INV','INV-Cannot find node');
3536: IF l_found = FALSE THEN
3537: fnd_message.set_name('INV','INV-Cannot find node');
3538: fnd_message.set_token('ROUTINE', 'Find_Tree_Node');
3539: fnd_msg_pub.ADD;
3540: RAISE fnd_api.g_exc_unexpected_error;
3541: End IF;
3542:
3543: If l_child_node_index <> 0 Then
3544: if g_nodes(l_current_node_index).first_child_index = 0 then
3589: RETURN TRUE;
3590:
3591: EXCEPTION
3592:
3593: WHEN fnd_api.g_exc_error THEN
3594: x_return_status := fnd_api.g_ret_sts_error;
3595: RETURN FALSE;
3596: WHEN fnd_api.g_exc_unexpected_error THEN
3597: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3590:
3591: EXCEPTION
3592:
3593: WHEN fnd_api.g_exc_error THEN
3594: x_return_status := fnd_api.g_ret_sts_error;
3595: RETURN FALSE;
3596: WHEN fnd_api.g_exc_unexpected_error THEN
3597: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3598: RETURN FALSE;
3592:
3593: WHEN fnd_api.g_exc_error THEN
3594: x_return_status := fnd_api.g_ret_sts_error;
3595: RETURN FALSE;
3596: WHEN fnd_api.g_exc_unexpected_error THEN
3597: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3598: RETURN FALSE;
3599:
3600: WHEN OTHERS THEN
3593: WHEN fnd_api.g_exc_error THEN
3594: x_return_status := fnd_api.g_ret_sts_error;
3595: RETURN FALSE;
3596: WHEN fnd_api.g_exc_unexpected_error THEN
3597: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3598: RETURN FALSE;
3599:
3600: WHEN OTHERS THEN
3601: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3597: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3598: RETURN FALSE;
3599:
3600: WHEN OTHERS THEN
3601: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3602:
3603: IF ((NOT g_is_pickrelease) OR nvl(g_debug,2) = 1) THEN
3604: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3605: THEN
3626: ) IS
3627:
3628: l_found BOOLEAN;
3629: l_root_id NUMBER;
3630: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3631: l_reservable VARCHAR2(10);
3632: b_reservable BOOLEAN;
3633: l_status_id NUMBER;
3634: l_zone_control NUMBER;
3835: , p_quantity_type IN INTEGER
3836: , p_set_check_mark IN BOOLEAN
3837: , p_cost_group_id IN NUMBER
3838: , p_lpn_id IN NUMBER
3839: , p_check_mark_node_only IN VARCHAR2 DEFAULT fnd_api.g_false
3840: --Bug 4294336 --Additional Parameters
3841: , p_transaction_action_id IN NUMBER DEFAULT NULL
3842: , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
3843: , p_transfer_locator_id IN NUMBER DEFAULT NULL
3841: , p_transaction_action_id IN NUMBER DEFAULT NULL
3842: , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
3843: , p_transfer_locator_id IN NUMBER DEFAULT NULL
3844: ) IS
3845: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3846: l_node_index INTEGER;
3847: l_found BOOLEAN;
3848: l_is_reservable_sub BOOLEAN;
3849: ll_is_reservable_sub BOOLEAN; -- invConv change
3889: -- invalid p_quantity_type. the caller's fault
3890: print_debug('... error=INVALID_QUANTITY_TYPE');
3891: fnd_message.set_name('INV', 'INV-INVALID_QUANTITY_TYPE');
3892: fnd_msg_pub.ADD;
3893: RAISE fnd_api.g_exc_error;
3894: END IF;
3895:
3896: l_hash_base := 1;
3897: l_hash_size := power(2, 20);
3924: , p_cost_group_id => p_cost_group_id
3925: , p_lpn_id => p_lpn_id
3926: );
3927:
3928: IF l_return_status = fnd_api.g_ret_sts_error THEN
3929: RAISE fnd_api.g_exc_error;
3930: End IF ;
3931:
3932: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3925: , p_lpn_id => p_lpn_id
3926: );
3927:
3928: IF l_return_status = fnd_api.g_ret_sts_error THEN
3929: RAISE fnd_api.g_exc_error;
3930: End IF ;
3931:
3932: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3933: RAISE fnd_api.g_exc_unexpected_error;
3928: IF l_return_status = fnd_api.g_ret_sts_error THEN
3929: RAISE fnd_api.g_exc_error;
3930: End IF ;
3931:
3932: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3933: RAISE fnd_api.g_exc_unexpected_error;
3934: END IF;
3935:
3936: IF l_found = FALSE THEN
3929: RAISE fnd_api.g_exc_error;
3930: End IF ;
3931:
3932: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3933: RAISE fnd_api.g_exc_unexpected_error;
3934: END IF;
3935:
3936: IF l_found = FALSE THEN
3937: fnd_message.set_name('INV','INV-Cannot find node');
3936: IF l_found = FALSE THEN
3937: fnd_message.set_name('INV','INV-Cannot find node');
3938: fnd_message.set_token('ROUTINE', 'Add_Quantities');
3939: fnd_msg_pub.ADD;
3940: RAISE fnd_api.g_exc_unexpected_error;
3941: End IF;
3942:
3943: -- get tree mode
3944: -- only use this value to see if mode is loose or not
3962: THEN
3963: print_debug('... error=INV-WRONG_LEVEL');
3964: fnd_message.set_name('INV', 'INV-WRONG_LEVEL');
3965: fnd_msg_pub.ADD;
3966: RAISE fnd_api.g_exc_error;
3967: End IF;
3968:
3969: -- need to find out whether the sub is reservable or not
3970: -- in order to compute rqoh
4041: , p_lpn_id => p_lpn_id); -- Onhand Material Status Support
4042: print_debug('in add_qty, after check_is_reservable. return_status='||l_return_status);
4043: -- invConv changes end
4044:
4045: IF l_return_status = fnd_api.g_ret_sts_error THEN
4046: RAISE fnd_api.g_exc_error;
4047: End IF ;
4048:
4049: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4042: print_debug('in add_qty, after check_is_reservable. return_status='||l_return_status);
4043: -- invConv changes end
4044:
4045: IF l_return_status = fnd_api.g_ret_sts_error THEN
4046: RAISE fnd_api.g_exc_error;
4047: End IF ;
4048:
4049: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4050: RAISE fnd_api.g_exc_unexpected_error;
4045: IF l_return_status = fnd_api.g_ret_sts_error THEN
4046: RAISE fnd_api.g_exc_error;
4047: End IF ;
4048:
4049: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4050: RAISE fnd_api.g_exc_unexpected_error;
4051: End IF;
4052: END IF; -- p_is_reservable_sub = NULL
4053:
4046: RAISE fnd_api.g_exc_error;
4047: End IF ;
4048:
4049: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4050: RAISE fnd_api.g_exc_unexpected_error;
4051: End IF;
4052: END IF; -- p_is_reservable_sub = NULL
4053:
4054: -- invConv changes begin : Added the ELSE cndition.
4067: , p_lpn_id => p_lpn_id); -- Onhand Material Status Support
4068: print_debug('in add_qty, after check_is_reservable. return_status='||l_return_status);
4069: -- invConv changes end
4070:
4071: IF l_return_status = fnd_api.g_ret_sts_error THEN
4072: RAISE fnd_api.g_exc_error;
4073: End IF ;
4074:
4075: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4068: print_debug('in add_qty, after check_is_reservable. return_status='||l_return_status);
4069: -- invConv changes end
4070:
4071: IF l_return_status = fnd_api.g_ret_sts_error THEN
4072: RAISE fnd_api.g_exc_error;
4073: End IF ;
4074:
4075: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4076: RAISE fnd_api.g_exc_unexpected_error;
4071: IF l_return_status = fnd_api.g_ret_sts_error THEN
4072: RAISE fnd_api.g_exc_error;
4073: End IF ;
4074:
4075: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4076: RAISE fnd_api.g_exc_unexpected_error;
4077: End IF;
4078: END IF; -- (INV_QUANTITY_TREE_PVT.g_is_mat_status_used = 2)
4079: -- invConv changes end.
4072: RAISE fnd_api.g_exc_error;
4073: End IF ;
4074:
4075: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4076: RAISE fnd_api.g_exc_unexpected_error;
4077: End IF;
4078: END IF; -- (INV_QUANTITY_TREE_PVT.g_is_mat_status_used = 2)
4079: -- invConv changes end.
4080:
4262: -- Bug 2486318. The do check does not work. Trasactions get committed
4263: -- even if there is a node violation. Added p_check_mark_node_only to mark the nodes.
4264:
4265: IF (p_set_check_mark = TRUE or
4266: p_check_mark_node_only = fnd_api.g_true AND
4267: p_primary_quantity < 0 )THEN
4268: g_nodes(l_loop_index).check_mark := TRUE;
4269: END IF;
4270:
4403: -- Bug 2486318. The do check does not work. Trasactions get committed
4404: -- even if there is a node violation. Added p_check_mark_node_only to mark the nodes.
4405:
4406: IF (p_set_check_mark = TRUE or
4407: p_check_mark_node_only = fnd_api.g_true AND p_primary_quantity >0) THEN
4408: g_nodes(l_loop_index).check_mark := TRUE;
4409: END IF;
4410:
4411: IF g_debug = 1 THEN
4427: AND g_nodes(l_node_index).node_level <> g_lpn_level
4428: THEN
4429: fnd_message.set_name('INV', 'INV-WRONG_LEVEL');
4430: fnd_msg_pub.ADD;
4431: RAISE fnd_api.g_exc_error;
4432: End IF;
4433:
4434: -- need to find out whether the sub is reservable or not
4435: -- in order to compute atr
4486: , p_lpn_id => p_lpn_id); -- Onhand Material Status Support
4487:
4488: -- invConv changes end
4489:
4490: IF l_return_status = fnd_api.g_ret_sts_error THEN
4491: RAISE fnd_api.g_exc_error;
4492: End IF ;
4493:
4494: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4487:
4488: -- invConv changes end
4489:
4490: IF l_return_status = fnd_api.g_ret_sts_error THEN
4491: RAISE fnd_api.g_exc_error;
4492: End IF ;
4493:
4494: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4495: RAISE fnd_api.g_exc_unexpected_error;
4490: IF l_return_status = fnd_api.g_ret_sts_error THEN
4491: RAISE fnd_api.g_exc_error;
4492: End IF ;
4493:
4494: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4495: RAISE fnd_api.g_exc_unexpected_error;
4496: End IF;
4497: END IF;
4498:
4491: RAISE fnd_api.g_exc_error;
4492: End IF ;
4493:
4494: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4495: RAISE fnd_api.g_exc_unexpected_error;
4496: End IF;
4497: END IF;
4498:
4499: g_nodes(l_sub_index).is_reservable_sub := l_is_reservable_sub;
4523: , p_subinventory_code => p_transfer_subinventory_code
4524: , x_is_reservable_sub => l_is_reservable_xfer_sub
4525: );
4526:
4527: IF l_return_status = fnd_api.g_ret_sts_error THEN
4528: RAISE fnd_api.g_exc_error;
4529: End IF ;
4530:
4531: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4524: , x_is_reservable_sub => l_is_reservable_xfer_sub
4525: );
4526:
4527: IF l_return_status = fnd_api.g_ret_sts_error THEN
4528: RAISE fnd_api.g_exc_error;
4529: End IF ;
4530:
4531: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4532: RAISE fnd_api.g_exc_unexpected_error;
4527: IF l_return_status = fnd_api.g_ret_sts_error THEN
4528: RAISE fnd_api.g_exc_error;
4529: End IF ;
4530:
4531: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4532: RAISE fnd_api.g_exc_unexpected_error;
4533: End IF;
4534: END IF;
4535: END IF;
4528: RAISE fnd_api.g_exc_error;
4529: End IF ;
4530:
4531: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4532: RAISE fnd_api.g_exc_unexpected_error;
4533: End IF;
4534: END IF;
4535: END IF;
4536: --Bug 4294336
5006: -- Bug 2486318. The do check does not work. Trasactions get committed
5007: -- even if there is a node violation. Added p_check_mark_node_only to mark the nodes.
5008:
5009: IF (p_set_check_mark = TRUE or
5010: p_check_mark_node_only = fnd_api.g_true AND p_primary_quantity < 0 )THEN
5011: g_nodes(l_loop_index).check_mark := TRUE;
5012: END IF;
5013:
5014: IF g_debug = 1 THEN
5064: print_debug('Normal end of add_quantities...');
5065: x_return_status := l_return_status;
5066:
5067: EXCEPTION
5068: WHEN fnd_api.g_exc_error THEN
5069: print_debug('in add_quantities... EXP_ERROR sql='||SQLERRM,9);
5070: x_return_status := fnd_api.g_ret_sts_error;
5071:
5072: WHEN fnd_api.g_exc_unexpected_error THEN
5066:
5067: EXCEPTION
5068: WHEN fnd_api.g_exc_error THEN
5069: print_debug('in add_quantities... EXP_ERROR sql='||SQLERRM,9);
5070: x_return_status := fnd_api.g_ret_sts_error;
5071:
5072: WHEN fnd_api.g_exc_unexpected_error THEN
5073: print_debug('in add_quantities... UNEXP_ERROR sql='||SQLERRM,9);
5074: x_return_status := fnd_api.g_ret_sts_unexp_error ;
5068: WHEN fnd_api.g_exc_error THEN
5069: print_debug('in add_quantities... EXP_ERROR sql='||SQLERRM,9);
5070: x_return_status := fnd_api.g_ret_sts_error;
5071:
5072: WHEN fnd_api.g_exc_unexpected_error THEN
5073: print_debug('in add_quantities... UNEXP_ERROR sql='||SQLERRM,9);
5074: x_return_status := fnd_api.g_ret_sts_unexp_error ;
5075:
5076: WHEN OTHERS THEN
5070: x_return_status := fnd_api.g_ret_sts_error;
5071:
5072: WHEN fnd_api.g_exc_unexpected_error THEN
5073: print_debug('in add_quantities... UNEXP_ERROR sql='||SQLERRM,9);
5074: x_return_status := fnd_api.g_ret_sts_unexp_error ;
5075:
5076: WHEN OTHERS THEN
5077: print_debug('in add_quantities... OTHERS ERROR sql='||SQLERRM,9);
5078: x_return_status := fnd_api.g_ret_sts_unexp_error ;
5074: x_return_status := fnd_api.g_ret_sts_unexp_error ;
5075:
5076: WHEN OTHERS THEN
5077: print_debug('in add_quantities... OTHERS ERROR sql='||SQLERRM,9);
5078: x_return_status := fnd_api.g_ret_sts_unexp_error ;
5079:
5080: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
5081: THEN
5082: fnd_msg_pub.add_exc_msg
5094: ( x_return_status OUT NOCOPY VARCHAR2
5095: , p_tree_id IN INTEGER
5096: ) IS
5097: l_api_name VARCHAR2(30) := 'BUILD_TREE';
5098: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
5099: l_is_reservable_sub BOOLEAN;
5100: l_cursor NUMBER;
5101: l_dummy INTEGER;
5102: l_index_start NUMBER := 0;
8063: print_debug('In build_tree. g_is_lot_control=FALSE');
8064: end if;
8065:
8066: zero_tree_node(l_return_status, g_rootinfos(p_tree_id).item_node_index);
8067: IF l_return_status = fnd_api.g_ret_sts_error THEN
8068: RAISE fnd_api.g_exc_error;
8069: End IF ;
8070:
8071: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8064: end if;
8065:
8066: zero_tree_node(l_return_status, g_rootinfos(p_tree_id).item_node_index);
8067: IF l_return_status = fnd_api.g_ret_sts_error THEN
8068: RAISE fnd_api.g_exc_error;
8069: End IF ;
8070:
8071: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8072: RAISE fnd_api.g_exc_unexpected_error;
8067: IF l_return_status = fnd_api.g_ret_sts_error THEN
8068: RAISE fnd_api.g_exc_error;
8069: End IF ;
8070:
8071: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8072: RAISE fnd_api.g_exc_unexpected_error;
8073: End IF;
8074:
8075: --bug 2668448
8068: RAISE fnd_api.g_exc_error;
8069: End IF ;
8070:
8071: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8072: RAISE fnd_api.g_exc_unexpected_error;
8073: End IF;
8074:
8075: --bug 2668448
8076: --quantity tree returned wrong values if the tree is rebuilt
8107: , l_cursor
8108: , g_rootinfos(p_tree_id).is_revision_control
8109: );
8110:
8111: IF l_return_status = fnd_api.g_ret_sts_error THEN
8112: RAISE fnd_api.g_exc_error;
8113: End IF ;
8114: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8115: RAISE fnd_api.g_exc_unexpected_error;
8108: , g_rootinfos(p_tree_id).is_revision_control
8109: );
8110:
8111: IF l_return_status = fnd_api.g_ret_sts_error THEN
8112: RAISE fnd_api.g_exc_error;
8113: End IF ;
8114: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8115: RAISE fnd_api.g_exc_unexpected_error;
8116: End IF;
8110:
8111: IF l_return_status = fnd_api.g_ret_sts_error THEN
8112: RAISE fnd_api.g_exc_error;
8113: End IF ;
8114: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8115: RAISE fnd_api.g_exc_unexpected_error;
8116: End IF;
8117: dbms_sql.define_array(l_cursor, 1,v_organization_id,l_num_recs_fetch,l_index_start);
8118: dbms_sql.define_array(l_cursor, 2,v_inventory_item_id,l_num_recs_fetch,l_index_start);
8111: IF l_return_status = fnd_api.g_ret_sts_error THEN
8112: RAISE fnd_api.g_exc_error;
8113: End IF ;
8114: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8115: RAISE fnd_api.g_exc_unexpected_error;
8116: End IF;
8117: dbms_sql.define_array(l_cursor, 1,v_organization_id,l_num_recs_fetch,l_index_start);
8118: dbms_sql.define_array(l_cursor, 2,v_inventory_item_id,l_num_recs_fetch,l_index_start);
8119: dbms_sql.define_array(l_cursor, 3,v_revision,l_num_recs_fetch,l_index_start);
8511: , p_transfer_subinventory_code => v_transfer_subinventory_code(l_index)
8512: , p_transfer_locator_id => v_transfer_locator_id(l_index)
8513: );
8514:
8515: IF l_return_status = fnd_api.g_ret_sts_error THEN
8516: RAISE fnd_api.g_exc_error;
8517: End IF ;
8518:
8519: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8512: , p_transfer_locator_id => v_transfer_locator_id(l_index)
8513: );
8514:
8515: IF l_return_status = fnd_api.g_ret_sts_error THEN
8516: RAISE fnd_api.g_exc_error;
8517: End IF ;
8518:
8519: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8520: RAISE fnd_api.g_exc_unexpected_error;
8515: IF l_return_status = fnd_api.g_ret_sts_error THEN
8516: RAISE fnd_api.g_exc_error;
8517: End IF ;
8518:
8519: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8520: RAISE fnd_api.g_exc_unexpected_error;
8521: End IF;
8522: ELSE
8523: -- invConv changes end.
8516: RAISE fnd_api.g_exc_error;
8517: End IF ;
8518:
8519: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8520: RAISE fnd_api.g_exc_unexpected_error;
8521: End IF;
8522: ELSE
8523: -- invConv changes end.
8524: print_debug(' Calling add_quantities for qty1='||v_primary_quantity(l_index)||', qty2='||v_secondary_quantity(l_index)||'...');
8542: , p_transfer_subinventory_code => v_transfer_subinventory_code(l_index)
8543: , p_transfer_locator_id => v_transfer_locator_id(l_index)
8544: );
8545:
8546: IF l_return_status = fnd_api.g_ret_sts_error THEN
8547: RAISE fnd_api.g_exc_error;
8548: End IF ;
8549:
8550: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8543: , p_transfer_locator_id => v_transfer_locator_id(l_index)
8544: );
8545:
8546: IF l_return_status = fnd_api.g_ret_sts_error THEN
8547: RAISE fnd_api.g_exc_error;
8548: End IF ;
8549:
8550: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8551: RAISE fnd_api.g_exc_unexpected_error;
8546: IF l_return_status = fnd_api.g_ret_sts_error THEN
8547: RAISE fnd_api.g_exc_error;
8548: End IF ;
8549:
8550: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8551: RAISE fnd_api.g_exc_unexpected_error;
8552: End IF;
8553: -- invConv changes begin : Closing test on g_is_lot_ctl
8554: END IF;
8547: RAISE fnd_api.g_exc_error;
8548: End IF ;
8549:
8550: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
8551: RAISE fnd_api.g_exc_unexpected_error;
8552: End IF;
8553: -- invConv changes begin : Closing test on g_is_lot_ctl
8554: END IF;
8555: -- invConv changes end.
8570: print_debug(' '||l_api_name || ' Exited with status = '||l_return_status,9);
8571: END IF;
8572:
8573: EXCEPTION
8574: WHEN fnd_api.g_exc_error THEN
8575: print_debug('build_tree: exc_err='||sqlerrm,9);
8576: x_return_status := fnd_api.g_ret_sts_error;
8577:
8578: WHEN fnd_api.g_exc_unexpected_error THEN
8572:
8573: EXCEPTION
8574: WHEN fnd_api.g_exc_error THEN
8575: print_debug('build_tree: exc_err='||sqlerrm,9);
8576: x_return_status := fnd_api.g_ret_sts_error;
8577:
8578: WHEN fnd_api.g_exc_unexpected_error THEN
8579: print_debug('build_tree: unexp_err='||sqlerrm,9);
8580: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8574: WHEN fnd_api.g_exc_error THEN
8575: print_debug('build_tree: exc_err='||sqlerrm,9);
8576: x_return_status := fnd_api.g_ret_sts_error;
8577:
8578: WHEN fnd_api.g_exc_unexpected_error THEN
8579: print_debug('build_tree: unexp_err='||sqlerrm,9);
8580: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8581:
8582: WHEN OTHERS THEN
8576: x_return_status := fnd_api.g_ret_sts_error;
8577:
8578: WHEN fnd_api.g_exc_unexpected_error THEN
8579: print_debug('build_tree: unexp_err='||sqlerrm,9);
8580: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8581:
8582: WHEN OTHERS THEN
8583: print_debug('build_tree: others='||sqlerrm,9);
8584: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8580: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8581:
8582: WHEN OTHERS THEN
8583: print_debug('build_tree: others='||sqlerrm,9);
8584: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8585:
8586: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
8587: THEN
8588: fnd_msg_pub.add_exc_msg
8604: , p_negative_allowed IN BOOLEAN
8605: , p_item_node_index IN INTEGER
8606: ) RETURN BOOLEAN
8607: IS
8608: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
8609: l_child_index INTEGER;
8610: l_no_violation BOOLEAN;
8611: l_node_index INTEGER;
8612: BEGIN
8666: END IF;
8667: RETURN l_no_violation;
8668:
8669: EXCEPTION
8670: WHEN fnd_api.g_exc_error THEN
8671: x_return_status := fnd_api.g_ret_sts_error;
8672:
8673: WHEN fnd_api.g_exc_unexpected_error THEN
8674: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8667: RETURN l_no_violation;
8668:
8669: EXCEPTION
8670: WHEN fnd_api.g_exc_error THEN
8671: x_return_status := fnd_api.g_ret_sts_error;
8672:
8673: WHEN fnd_api.g_exc_unexpected_error THEN
8674: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8675:
8669: EXCEPTION
8670: WHEN fnd_api.g_exc_error THEN
8671: x_return_status := fnd_api.g_ret_sts_error;
8672:
8673: WHEN fnd_api.g_exc_unexpected_error THEN
8674: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8675:
8676: WHEN OTHERS THEN
8677: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8670: WHEN fnd_api.g_exc_error THEN
8671: x_return_status := fnd_api.g_ret_sts_error;
8672:
8673: WHEN fnd_api.g_exc_unexpected_error THEN
8674: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8675:
8676: WHEN OTHERS THEN
8677: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8678:
8673: WHEN fnd_api.g_exc_unexpected_error THEN
8674: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8675:
8676: WHEN OTHERS THEN
8677: x_return_status := fnd_api.g_ret_sts_unexp_error ;
8678:
8679: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
8680: THEN
8681: fnd_msg_pub.add_exc_msg
8853: AND nvl(supply_source_type_id,13) = 13 ; -- Bug 4194323
8854: is_lot_control Boolean :=false; -- Bug 4194323
8855: BEGIN
8856:
8857: x_return_status := fnd_api.g_ret_sts_success;
8858: g_rsv_info.DELETE;
8859: g_rsv_counter := 0;
8860: l_root_id := g_demand_info(p_tree_id).root_id;
8861: is_lot_control:=g_rootinfos(l_root_id).is_lot_control;
8895: , p_lpn_id => l_lpn_id
8896: );
8897:
8898: print_debug('... after add_quantities... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
8899: IF x_return_status = fnd_api.g_ret_sts_error THEN
8900: RAISE fnd_api.g_exc_error;
8901: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
8902: RAISE fnd_api.g_exc_unexpected_error;
8903: END IF;
8896: );
8897:
8898: print_debug('... after add_quantities... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
8899: IF x_return_status = fnd_api.g_ret_sts_error THEN
8900: RAISE fnd_api.g_exc_error;
8901: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
8902: RAISE fnd_api.g_exc_unexpected_error;
8903: END IF;
8904:
8897:
8898: print_debug('... after add_quantities... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
8899: IF x_return_status = fnd_api.g_ret_sts_error THEN
8900: RAISE fnd_api.g_exc_error;
8901: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
8902: RAISE fnd_api.g_exc_unexpected_error;
8903: END IF;
8904:
8905: g_rsv_counter := g_rsv_counter + 1;
8898: print_debug('... after add_quantities... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
8899: IF x_return_status = fnd_api.g_ret_sts_error THEN
8900: RAISE fnd_api.g_exc_error;
8901: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
8902: RAISE fnd_api.g_exc_unexpected_error;
8903: END IF;
8904:
8905: g_rsv_counter := g_rsv_counter + 1;
8906: g_rsv_info(g_rsv_counter).revision := l_revision;
8944: );
8945:
8946: print_debug('... after add_quantities 2... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
8947:
8948: IF x_return_status = fnd_api.g_ret_sts_error THEN
8949: RAISE fnd_api.g_exc_error;
8950: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
8951: RAISE fnd_api.g_exc_unexpected_error;
8952: END IF;
8945:
8946: print_debug('... after add_quantities 2... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
8947:
8948: IF x_return_status = fnd_api.g_ret_sts_error THEN
8949: RAISE fnd_api.g_exc_error;
8950: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
8951: RAISE fnd_api.g_exc_unexpected_error;
8952: END IF;
8953:
8946: print_debug('... after add_quantities 2... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
8947:
8948: IF x_return_status = fnd_api.g_ret_sts_error THEN
8949: RAISE fnd_api.g_exc_error;
8950: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
8951: RAISE fnd_api.g_exc_unexpected_error;
8952: END IF;
8953:
8954: g_rsv_counter := g_rsv_counter + 1;
8947:
8948: IF x_return_status = fnd_api.g_ret_sts_error THEN
8949: RAISE fnd_api.g_exc_error;
8950: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
8951: RAISE fnd_api.g_exc_unexpected_error;
8952: END IF;
8953:
8954: g_rsv_counter := g_rsv_counter + 1;
8955: g_rsv_info(g_rsv_counter).revision := l_revision;
8993: );
8994:
8995: print_debug('... after add_quantities 3... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
8996:
8997: IF x_return_status = fnd_api.g_ret_sts_error THEN
8998: RAISE fnd_api.g_exc_error;
8999: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9000: RAISE fnd_api.g_exc_unexpected_error;
9001: END IF;
8994:
8995: print_debug('... after add_quantities 3... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
8996:
8997: IF x_return_status = fnd_api.g_ret_sts_error THEN
8998: RAISE fnd_api.g_exc_error;
8999: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9000: RAISE fnd_api.g_exc_unexpected_error;
9001: END IF;
9002:
8995: print_debug('... after add_quantities 3... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
8996:
8997: IF x_return_status = fnd_api.g_ret_sts_error THEN
8998: RAISE fnd_api.g_exc_error;
8999: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9000: RAISE fnd_api.g_exc_unexpected_error;
9001: END IF;
9002:
9003: g_rsv_counter := g_rsv_counter + 1;
8996:
8997: IF x_return_status = fnd_api.g_ret_sts_error THEN
8998: RAISE fnd_api.g_exc_error;
8999: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9000: RAISE fnd_api.g_exc_unexpected_error;
9001: END IF;
9002:
9003: g_rsv_counter := g_rsv_counter + 1;
9004: g_rsv_info(g_rsv_counter).revision := l_revision;
9048: );
9049:
9050: print_debug('... after add_quantities 4... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
9051:
9052: IF x_return_status = fnd_api.g_ret_sts_error THEN
9053: RAISE fnd_api.g_exc_error;
9054: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9055: RAISE fnd_api.g_exc_unexpected_error;
9056: END IF;
9049:
9050: print_debug('... after add_quantities 4... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
9051:
9052: IF x_return_status = fnd_api.g_ret_sts_error THEN
9053: RAISE fnd_api.g_exc_error;
9054: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9055: RAISE fnd_api.g_exc_unexpected_error;
9056: END IF;
9057:
9050: print_debug('... after add_quantities 4... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
9051:
9052: IF x_return_status = fnd_api.g_ret_sts_error THEN
9053: RAISE fnd_api.g_exc_error;
9054: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9055: RAISE fnd_api.g_exc_unexpected_error;
9056: END IF;
9057:
9058: g_rsv_counter := g_rsv_counter + 1;
9051:
9052: IF x_return_status = fnd_api.g_ret_sts_error THEN
9053: RAISE fnd_api.g_exc_error;
9054: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9055: RAISE fnd_api.g_exc_unexpected_error;
9056: END IF;
9057:
9058: g_rsv_counter := g_rsv_counter + 1;
9059: g_rsv_info(g_rsv_counter).revision := l_revision;
9098: );
9099:
9100: print_debug('... after add_quantities 5... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
9101:
9102: IF x_return_status = fnd_api.g_ret_sts_error THEN
9103: RAISE fnd_api.g_exc_error;
9104: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9105: RAISE fnd_api.g_exc_unexpected_error;
9106: END IF;
9099:
9100: print_debug('... after add_quantities 5... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
9101:
9102: IF x_return_status = fnd_api.g_ret_sts_error THEN
9103: RAISE fnd_api.g_exc_error;
9104: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9105: RAISE fnd_api.g_exc_unexpected_error;
9106: END IF;
9107:
9100: print_debug('... after add_quantities 5... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
9101:
9102: IF x_return_status = fnd_api.g_ret_sts_error THEN
9103: RAISE fnd_api.g_exc_error;
9104: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9105: RAISE fnd_api.g_exc_unexpected_error;
9106: END IF;
9107:
9108: g_rsv_counter := g_rsv_counter + 1;
9101:
9102: IF x_return_status = fnd_api.g_ret_sts_error THEN
9103: RAISE fnd_api.g_exc_error;
9104: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9105: RAISE fnd_api.g_exc_unexpected_error;
9106: END IF;
9107:
9108: g_rsv_counter := g_rsv_counter + 1;
9109: g_rsv_info(g_rsv_counter).revision := l_revision;
9148: );
9149:
9150: print_debug('... after add_quantities 6... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
9151:
9152: IF x_return_status = fnd_api.g_ret_sts_error THEN
9153: RAISE fnd_api.g_exc_error;
9154: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9155: RAISE fnd_api.g_exc_unexpected_error;
9156: END IF;
9149:
9150: print_debug('... after add_quantities 6... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
9151:
9152: IF x_return_status = fnd_api.g_ret_sts_error THEN
9153: RAISE fnd_api.g_exc_error;
9154: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9155: RAISE fnd_api.g_exc_unexpected_error;
9156: END IF;
9157:
9150: print_debug('... after add_quantities 6... x_return_status='||x_return_status||', qty='||l_quantity||', qty2='||l_quantity2);
9151:
9152: IF x_return_status = fnd_api.g_ret_sts_error THEN
9153: RAISE fnd_api.g_exc_error;
9154: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9155: RAISE fnd_api.g_exc_unexpected_error;
9156: END IF;
9157:
9158: g_rsv_counter := g_rsv_counter + 1;
9151:
9152: IF x_return_status = fnd_api.g_ret_sts_error THEN
9153: RAISE fnd_api.g_exc_error;
9154: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9155: RAISE fnd_api.g_exc_unexpected_error;
9156: END IF;
9157:
9158: g_rsv_counter := g_rsv_counter + 1;
9159: g_rsv_info(g_rsv_counter).revision := l_revision;
9170: END IF;
9171: print_debug('... ending add_demand_qty');
9172:
9173: EXCEPTION
9174: WHEN fnd_api.g_exc_error THEN
9175: x_return_status := fnd_api.g_ret_sts_error;
9176:
9177: WHEN fnd_api.g_exc_unexpected_error THEN
9178: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9171: print_debug('... ending add_demand_qty');
9172:
9173: EXCEPTION
9174: WHEN fnd_api.g_exc_error THEN
9175: x_return_status := fnd_api.g_ret_sts_error;
9176:
9177: WHEN fnd_api.g_exc_unexpected_error THEN
9178: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9179:
9173: EXCEPTION
9174: WHEN fnd_api.g_exc_error THEN
9175: x_return_status := fnd_api.g_ret_sts_error;
9176:
9177: WHEN fnd_api.g_exc_unexpected_error THEN
9178: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9179:
9180: WHEN OTHERS THEN
9181: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9174: WHEN fnd_api.g_exc_error THEN
9175: x_return_status := fnd_api.g_ret_sts_error;
9176:
9177: WHEN fnd_api.g_exc_unexpected_error THEN
9178: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9179:
9180: WHEN OTHERS THEN
9181: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9182: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
9177: WHEN fnd_api.g_exc_unexpected_error THEN
9178: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9179:
9180: WHEN OTHERS THEN
9181: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9182: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
9183: THEN
9184: fnd_msg_pub.add_exc_msg
9185: ( g_pkg_name
9202:
9203: BEGIN
9204: print_debug('... entering subtract_demand_qty');
9205:
9206: x_return_status := fnd_api.g_ret_sts_success;
9207:
9208: l_root_id := g_demand_info(p_tree_id).root_id;
9209:
9210: FOR i in 1..g_rsv_counter LOOP
9225: , p_cost_group_id => NULL
9226: , p_lpn_id => g_rsv_info(i).lpn_id
9227: );
9228:
9229: IF x_return_status = fnd_api.g_ret_sts_error THEN
9230: RAISE fnd_api.g_exc_error;
9231: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9232: RAISE fnd_api.g_exc_unexpected_error;
9233: END IF;
9226: , p_lpn_id => g_rsv_info(i).lpn_id
9227: );
9228:
9229: IF x_return_status = fnd_api.g_ret_sts_error THEN
9230: RAISE fnd_api.g_exc_error;
9231: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9232: RAISE fnd_api.g_exc_unexpected_error;
9233: END IF;
9234:
9227: );
9228:
9229: IF x_return_status = fnd_api.g_ret_sts_error THEN
9230: RAISE fnd_api.g_exc_error;
9231: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9232: RAISE fnd_api.g_exc_unexpected_error;
9233: END IF;
9234:
9235: END LOOP;
9228:
9229: IF x_return_status = fnd_api.g_ret_sts_error THEN
9230: RAISE fnd_api.g_exc_error;
9231: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
9232: RAISE fnd_api.g_exc_unexpected_error;
9233: END IF;
9234:
9235: END LOOP;
9236:
9234:
9235: END LOOP;
9236:
9237: EXCEPTION
9238: WHEN fnd_api.g_exc_error THEN
9239: x_return_status := fnd_api.g_ret_sts_error;
9240:
9241: WHEN fnd_api.g_exc_unexpected_error THEN
9242: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9235: END LOOP;
9236:
9237: EXCEPTION
9238: WHEN fnd_api.g_exc_error THEN
9239: x_return_status := fnd_api.g_ret_sts_error;
9240:
9241: WHEN fnd_api.g_exc_unexpected_error THEN
9242: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9243:
9237: EXCEPTION
9238: WHEN fnd_api.g_exc_error THEN
9239: x_return_status := fnd_api.g_ret_sts_error;
9240:
9241: WHEN fnd_api.g_exc_unexpected_error THEN
9242: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9243:
9244: WHEN OTHERS THEN
9245: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9238: WHEN fnd_api.g_exc_error THEN
9239: x_return_status := fnd_api.g_ret_sts_error;
9240:
9241: WHEN fnd_api.g_exc_unexpected_error THEN
9242: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9243:
9244: WHEN OTHERS THEN
9245: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9246: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
9241: WHEN fnd_api.g_exc_unexpected_error THEN
9242: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9243:
9244: WHEN OTHERS THEN
9245: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9246: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
9247: THEN
9248: fnd_msg_pub.add_exc_msg
9249: ( g_pkg_name
9436: , p_transfer_locator_id IN NUMBER
9437: ) IS
9438: l_api_version_number CONSTANT NUMBER := 1.0;
9439: l_api_name CONSTANT VARCHAR2(30) := 'QUERY_TREE';
9440: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
9441: l_node_index INTEGER;
9442: l_atr NUMBER;
9443: l_att NUMBER;
9444: l_satr NUMBER; -- invConv change
9477: print_debug(l_api_name || ' Entered',9);
9478: END IF;
9479:
9480: -- Standard call to check for call compatibility
9481: IF NOT fnd_api.compatible_api_call(l_api_version_number
9482: , p_api_version_number
9483: , l_api_name
9484: , G_PKG_NAME
9485: ) THEN
9482: , p_api_version_number
9483: , l_api_name
9484: , G_PKG_NAME
9485: ) THEN
9486: RAISE fnd_api.g_exc_unexpected_error;
9487: END IF;
9488:
9489: -- Initialize message list.
9490: IF fnd_api.to_boolean(p_init_msg_lst) THEN
9486: RAISE fnd_api.g_exc_unexpected_error;
9487: END IF;
9488:
9489: -- Initialize message list.
9490: IF fnd_api.to_boolean(p_init_msg_lst) THEN
9491: fnd_msg_pub.initialize;
9492: END IF;
9493:
9494: l_root_id := g_demand_info(p_tree_id).root_id;
9496: IF is_tree_valid(l_root_id) = FALSE THEN
9497: fnd_message.set_name('INV', 'INV-Qtyroot not found');
9498: fnd_message.set_token('ROUTINE', 'Query_Tree');
9499: fnd_msg_pub.ADD;
9500: RAISE fnd_api.g_exc_error;
9501: END IF;
9502:
9503: print_debug('>>>>>> In query_tree, mode='||g_demand_info(p_tree_id).tree_mode||', tree_id='||p_tree_id
9504: ||'..for item='||g_rootinfos(l_root_id).inventory_item_id||' rev='||p_revision||' lot='||p_lot_number
9527: x_return_status => l_return_status
9528: , p_tree_id => g_rsv_tree_id
9529: );
9530:
9531: IF l_return_status = fnd_api.g_ret_sts_error THEN
9532: RAISE fnd_api.g_exc_error;
9533: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9534: RAISE fnd_api.g_exc_unexpected_error;
9535: END IF;
9528: , p_tree_id => g_rsv_tree_id
9529: );
9530:
9531: IF l_return_status = fnd_api.g_ret_sts_error THEN
9532: RAISE fnd_api.g_exc_error;
9533: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9534: RAISE fnd_api.g_exc_unexpected_error;
9535: END IF;
9536: g_rsv_tree_id := 0;
9529: );
9530:
9531: IF l_return_status = fnd_api.g_ret_sts_error THEN
9532: RAISE fnd_api.g_exc_error;
9533: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9534: RAISE fnd_api.g_exc_unexpected_error;
9535: END IF;
9536: g_rsv_tree_id := 0;
9537: g_rsv_counter := 0;
9530:
9531: IF l_return_status = fnd_api.g_ret_sts_error THEN
9532: RAISE fnd_api.g_exc_error;
9533: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9534: RAISE fnd_api.g_exc_unexpected_error;
9535: END IF;
9536: g_rsv_tree_id := 0;
9537: g_rsv_counter := 0;
9538: g_rsv_info.DELETE;
9546: x_return_status => l_return_status
9547: , p_tree_id => g_rsv_tree_id
9548: );
9549:
9550: IF l_return_status = fnd_api.g_ret_sts_error THEN
9551: RAISE fnd_api.g_exc_error;
9552: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9553: RAISE fnd_api.g_exc_unexpected_error;
9554: END IF;
9547: , p_tree_id => g_rsv_tree_id
9548: );
9549:
9550: IF l_return_status = fnd_api.g_ret_sts_error THEN
9551: RAISE fnd_api.g_exc_error;
9552: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9553: RAISE fnd_api.g_exc_unexpected_error;
9554: END IF;
9555: g_rsv_tree_id := 0;
9548: );
9549:
9550: IF l_return_status = fnd_api.g_ret_sts_error THEN
9551: RAISE fnd_api.g_exc_error;
9552: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9553: RAISE fnd_api.g_exc_unexpected_error;
9554: END IF;
9555: g_rsv_tree_id := 0;
9556: g_rsv_counter := 0;
9549:
9550: IF l_return_status = fnd_api.g_ret_sts_error THEN
9551: RAISE fnd_api.g_exc_error;
9552: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9553: RAISE fnd_api.g_exc_unexpected_error;
9554: END IF;
9555: g_rsv_tree_id := 0;
9556: g_rsv_counter := 0;
9557: g_rsv_info.DELETE;
9563: x_return_status => l_return_status
9564: , p_tree_id => p_tree_id
9565: );
9566:
9567: IF l_return_status = fnd_api.g_ret_sts_error THEN
9568: RAISE fnd_api.g_exc_error;
9569: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9570: RAISE fnd_api.g_exc_unexpected_error;
9571: END IF;
9564: , p_tree_id => p_tree_id
9565: );
9566:
9567: IF l_return_status = fnd_api.g_ret_sts_error THEN
9568: RAISE fnd_api.g_exc_error;
9569: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9570: RAISE fnd_api.g_exc_unexpected_error;
9571: END IF;
9572: g_rsv_tree_id := p_tree_id;
9565: );
9566:
9567: IF l_return_status = fnd_api.g_ret_sts_error THEN
9568: RAISE fnd_api.g_exc_error;
9569: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9570: RAISE fnd_api.g_exc_unexpected_error;
9571: END IF;
9572: g_rsv_tree_id := p_tree_id;
9573: end if;
9566:
9567: IF l_return_status = fnd_api.g_ret_sts_error THEN
9568: RAISE fnd_api.g_exc_error;
9569: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9570: RAISE fnd_api.g_exc_unexpected_error;
9571: END IF;
9572: g_rsv_tree_id := p_tree_id;
9573: end if;
9574: END IF; -- tree_mode
9586: , p_lpn_id => p_lpn_id
9587: , p_cost_group_id => p_cost_group_id
9588: );
9589:
9590: IF l_return_status = fnd_api.g_ret_sts_error THEN
9591: RAISE fnd_api.g_exc_error;
9592: End IF ;
9593:
9594: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9587: , p_cost_group_id => p_cost_group_id
9588: );
9589:
9590: IF l_return_status = fnd_api.g_ret_sts_error THEN
9591: RAISE fnd_api.g_exc_error;
9592: End IF ;
9593:
9594: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9595: RAISE fnd_api.g_exc_unexpected_error;
9590: IF l_return_status = fnd_api.g_ret_sts_error THEN
9591: RAISE fnd_api.g_exc_error;
9592: End IF ;
9593:
9594: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9595: RAISE fnd_api.g_exc_unexpected_error;
9596: End IF;
9597:
9598: IF l_found = FALSE THEN
9591: RAISE fnd_api.g_exc_error;
9592: End IF ;
9593:
9594: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9595: RAISE fnd_api.g_exc_unexpected_error;
9596: End IF;
9597:
9598: IF l_found = FALSE THEN
9599: fnd_message.set_name('INV','INV-Cannot find node');
9598: IF l_found = FALSE THEN
9599: fnd_message.set_name('INV','INV-Cannot find node');
9600: fnd_message.set_token('ROUTINE', 'Query_Tree');
9601: fnd_msg_pub.ADD;
9602: RAISE fnd_api.g_exc_unexpected_error;
9603: End IF;
9604:
9605: x_qoh := g_nodes(l_node_index).qoh;
9606: x_rqoh := g_nodes(l_node_index).rqoh;
9686: , p_lpn_id => p_lpn_id); -- Onhand Material Status Support
9687:
9688: -- invConv changes end
9689:
9690: IF l_return_status = fnd_api.g_ret_sts_error THEN
9691: RAISE fnd_api.g_exc_error;
9692: End IF ;
9693:
9694: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9687:
9688: -- invConv changes end
9689:
9690: IF l_return_status = fnd_api.g_ret_sts_error THEN
9691: RAISE fnd_api.g_exc_error;
9692: End IF ;
9693:
9694: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9695: RAISE fnd_api.g_exc_unexpected_error;
9690: IF l_return_status = fnd_api.g_ret_sts_error THEN
9691: RAISE fnd_api.g_exc_error;
9692: End IF ;
9693:
9694: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9695: RAISE fnd_api.g_exc_unexpected_error;
9696: End IF;
9697:
9698: END IF; -- l_is_reservable_sub
9691: RAISE fnd_api.g_exc_error;
9692: End IF ;
9693:
9694: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9695: RAISE fnd_api.g_exc_unexpected_error;
9696: End IF;
9697:
9698: END IF; -- l_is_reservable_sub
9699:
9719: , p_lpn_id => p_lpn_id); -- Onhand Material Status Support
9720:
9721: -- invConv changes end
9722:
9723: IF l_return_status = fnd_api.g_ret_sts_error THEN
9724: RAISE fnd_api.g_exc_error;
9725: End IF ;
9726:
9727: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9720:
9721: -- invConv changes end
9722:
9723: IF l_return_status = fnd_api.g_ret_sts_error THEN
9724: RAISE fnd_api.g_exc_error;
9725: End IF ;
9726:
9727: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9728: RAISE fnd_api.g_exc_unexpected_error;
9723: IF l_return_status = fnd_api.g_ret_sts_error THEN
9724: RAISE fnd_api.g_exc_error;
9725: End IF ;
9726:
9727: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9728: RAISE fnd_api.g_exc_unexpected_error;
9729: End IF;
9730: ELSE
9731: l_is_reservable_transfer_sub := FALSE;
9724: RAISE fnd_api.g_exc_error;
9725: End IF ;
9726:
9727: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
9728: RAISE fnd_api.g_exc_unexpected_error;
9729: End IF;
9730: ELSE
9731: l_is_reservable_transfer_sub := FALSE;
9732: END IF; -- p_transfer_subinventory_code
9929: print_debug(' ',9);
9930: END IF;
9931: EXCEPTION
9932:
9933: WHEN fnd_api.g_exc_error THEN
9934: print_debug('QT: ending... g_exc_error'||SQLERRM,9);
9935: x_return_status := fnd_api.g_ret_sts_error;
9936:
9937: -- Get message count and data
9931: EXCEPTION
9932:
9933: WHEN fnd_api.g_exc_error THEN
9934: print_debug('QT: ending... g_exc_error'||SQLERRM,9);
9935: x_return_status := fnd_api.g_ret_sts_error;
9936:
9937: -- Get message count and data
9938: fnd_msg_pub.count_and_get
9939: ( p_count => x_msg_count
9939: ( p_count => x_msg_count
9940: , p_data => x_msg_data
9941: );
9942:
9943: WHEN fnd_api.g_exc_unexpected_error THEN
9944: print_debug('QT: ending... g_exc_unexpected_error '||SQLERRM,9);
9945: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9946:
9947: -- Get message count and data
9941: );
9942:
9943: WHEN fnd_api.g_exc_unexpected_error THEN
9944: print_debug('QT: ending... g_exc_unexpected_error '||SQLERRM,9);
9945: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9946:
9947: -- Get message count and data
9948: fnd_msg_pub.count_and_get
9949: ( p_count => x_msg_count
9951: );
9952:
9953: WHEN OTHERS THEN
9954: print_debug('QT: ending... OTHERS.'||SQLERRM,9);
9955: x_return_status := fnd_api.g_ret_sts_unexp_error ;
9956:
9957: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
9958: THEN
9959: fnd_msg_pub.add_exc_msg
10109:
10110: print_debug(' End of query_tree2');
10111:
10112: EXCEPTION
10113: WHEN fnd_api.g_exc_error THEN
10114: x_return_status := fnd_api.g_ret_sts_error;
10115: -- Get message count and data
10116: fnd_msg_pub.count_and_get
10117: ( p_count => x_msg_count
10110: print_debug(' End of query_tree2');
10111:
10112: EXCEPTION
10113: WHEN fnd_api.g_exc_error THEN
10114: x_return_status := fnd_api.g_ret_sts_error;
10115: -- Get message count and data
10116: fnd_msg_pub.count_and_get
10117: ( p_count => x_msg_count
10118: , p_data => x_msg_data
10116: fnd_msg_pub.count_and_get
10117: ( p_count => x_msg_count
10118: , p_data => x_msg_data
10119: );
10120: WHEN fnd_api.g_exc_unexpected_error THEN
10121: x_return_status := fnd_api.g_ret_sts_unexp_error ;
10122:
10123: -- Get message count and data
10124: fnd_msg_pub.count_and_get
10117: ( p_count => x_msg_count
10118: , p_data => x_msg_data
10119: );
10120: WHEN fnd_api.g_exc_unexpected_error THEN
10121: x_return_status := fnd_api.g_ret_sts_unexp_error ;
10122:
10123: -- Get message count and data
10124: fnd_msg_pub.count_and_get
10125: ( p_count => x_msg_count
10125: ( p_count => x_msg_count
10126: , p_data => x_msg_data
10127: );
10128: WHEN OTHERS THEN
10129: x_return_status := fnd_api.g_ret_sts_unexp_error ;
10130: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
10131: THEN
10132: fnd_msg_pub.add_exc_msg
10133: ( g_pkg_name
10269:
10270: ) IS
10271: l_api_version_number CONSTANT NUMBER := 1.0;
10272: l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TREE';
10273: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
10274: l_rootinfo_index INTEGER;
10275: l_demand_source_type_id NUMBER;
10276: l_demand_source_header_id NUMBER;
10277: l_demand_source_line_id NUMBER;
10369: print_debug('In create_tree. p_is_lot_control=FALSE');
10370: end if;
10371:
10372: -- Standard call to check for call compatibility
10373: IF NOT fnd_api.compatible_api_call(l_api_version_number
10374: , p_api_version_number
10375: , l_api_name
10376: , G_PKG_NAME
10377: ) THEN
10374: , p_api_version_number
10375: , l_api_name
10376: , G_PKG_NAME
10377: ) THEN
10378: RAISE fnd_api.g_exc_unexpected_error;
10379: END IF;
10380:
10381: -- Initialize message list.
10382: IF fnd_api.to_boolean(p_init_msg_lst) THEN
10378: RAISE fnd_api.g_exc_unexpected_error;
10379: END IF;
10380:
10381: -- Initialize message list.
10382: IF fnd_api.to_boolean(p_init_msg_lst) THEN
10383: fnd_msg_pub.initialize;
10384: END IF;
10385:
10386: g_is_pickrelease := nvl(inv_cache.is_pickrelease,FALSE);
10398: IF p_tree_mode IN (g_transaction_mode, g_loose_only_mode) THEN
10399: IF p_demand_source_type_id IS NULL THEN
10400: fnd_message.set_name('INV', 'INV-MISSING DEMAND SOURCE TYPE');
10401: fnd_msg_pub.ADD;
10402: RAISE fnd_api.g_exc_error;
10403: END IF;
10404:
10405: IF p_demand_source_header_id IS NULL THEN
10406: IF p_demand_source_name IS NULL THEN
10405: IF p_demand_source_header_id IS NULL THEN
10406: IF p_demand_source_name IS NULL THEN
10407: fnd_message.set_name('INV', 'INV-MISSING DEMAND SRC HEADER');
10408: fnd_msg_pub.ADD;
10409: RAISE fnd_api.g_exc_error;
10410: END IF;
10411: END IF;
10412:
10413: IF p_demand_source_header_id IS NULL
10413: IF p_demand_source_header_id IS NULL
10414: AND p_demand_source_line_id IS NOT NULL THEN
10415: fnd_message.set_name('INV', 'INV-MISSING DEMAND SRC HEADER');
10416: fnd_msg_pub.ADD;
10417: RAISE fnd_api.g_exc_error;
10418: END IF;
10419: END IF;
10420:
10421: -- invConv changes begin:
10437: FND_MESSAGE.SET_NAME('INV', 'ITEM_NOTFOUND');
10438: FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID', p_inventory_item_id);
10439: FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID', p_organization_id);
10440: FND_MSG_PUB.ADD;
10441: RAISE FND_API.G_EXC_ERROR;
10442: END IF;
10443: -- invConv comment : g_is_mat_status_used = 1 == Material Status is USED
10444: IF (inv_quantity_tree_pvt.g_is_mat_status_used = 1 AND l_lot_control = 2)
10445: THEN
10454:
10455: IF (p_exclusive = g_exclusive) THEN
10456: lock_tree(
10457: p_api_version_number => 1.0
10458: , p_init_msg_lst => fnd_api.g_false
10459: , x_return_status => l_return_status
10460: , x_msg_count => x_msg_count
10461: , x_msg_data => x_msg_data
10462: , p_organization_id => p_organization_id
10462: , p_organization_id => p_organization_id
10463: , p_inventory_item_id => p_inventory_item_id
10464: );
10465:
10466: IF l_return_status = fnd_api.g_ret_sts_error THEN
10467: RAISE fnd_api.g_exc_error;
10468: END IF ;
10469:
10470: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10463: , p_inventory_item_id => p_inventory_item_id
10464: );
10465:
10466: IF l_return_status = fnd_api.g_ret_sts_error THEN
10467: RAISE fnd_api.g_exc_error;
10468: END IF ;
10469:
10470: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10471: RAISE fnd_api.g_exc_unexpected_error;
10466: IF l_return_status = fnd_api.g_ret_sts_error THEN
10467: RAISE fnd_api.g_exc_error;
10468: END IF ;
10469:
10470: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10471: RAISE fnd_api.g_exc_unexpected_error;
10472: END IF;
10473: END IF;
10474:
10467: RAISE fnd_api.g_exc_error;
10468: END IF ;
10469:
10470: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10471: RAISE fnd_api.g_exc_unexpected_error;
10472: END IF;
10473: END IF;
10474:
10475: IF p_lot_expiration_date IS NOT NULL THEN
10497: , p_pick_release => p_pick_release
10498: -- odab temp removed , p_grade_code => p_grade_code -- invConv change
10499: );
10500:
10501: IF l_return_status = fnd_api.g_ret_sts_error THEN
10502: RAISE fnd_api.g_exc_error;
10503: End IF ;
10504:
10505: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10498: -- odab temp removed , p_grade_code => p_grade_code -- invConv change
10499: );
10500:
10501: IF l_return_status = fnd_api.g_ret_sts_error THEN
10502: RAISE fnd_api.g_exc_error;
10503: End IF ;
10504:
10505: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10506: RAISE fnd_api.g_exc_unexpected_error;
10501: IF l_return_status = fnd_api.g_ret_sts_error THEN
10502: RAISE fnd_api.g_exc_error;
10503: End IF ;
10504:
10505: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10506: RAISE fnd_api.g_exc_unexpected_error;
10507: End IF;
10508: -- create the tree if the rootinfo can not be found
10509: IF (l_rootinfo_index = 0) THEN
10502: RAISE fnd_api.g_exc_error;
10503: End IF ;
10504:
10505: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10506: RAISE fnd_api.g_exc_unexpected_error;
10507: End IF;
10508: -- create the tree if the rootinfo can not be found
10509: IF (l_rootinfo_index = 0) THEN
10510: new_tree(
10527: , p_pick_release => p_pick_release
10528: , p_grade_code => p_grade_code -- invConv change
10529: , x_tree_id => l_rootinfo_index
10530: );
10531: IF l_return_status = fnd_api.g_ret_sts_error THEN
10532: RAISE fnd_api.g_exc_error;
10533: END IF ;
10534:
10535: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10528: , p_grade_code => p_grade_code -- invConv change
10529: , x_tree_id => l_rootinfo_index
10530: );
10531: IF l_return_status = fnd_api.g_ret_sts_error THEN
10532: RAISE fnd_api.g_exc_error;
10533: END IF ;
10534:
10535: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10536: RAISE fnd_api.g_exc_unexpected_error;
10531: IF l_return_status = fnd_api.g_ret_sts_error THEN
10532: RAISE fnd_api.g_exc_error;
10533: END IF ;
10534:
10535: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10536: RAISE fnd_api.g_exc_unexpected_error;
10537: END IF;
10538:
10539: END IF;
10532: RAISE fnd_api.g_exc_error;
10533: END IF ;
10534:
10535: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10536: RAISE fnd_api.g_exc_unexpected_error;
10537: END IF;
10538:
10539: END IF;
10540:
10569: g_rootinfos(l_rootinfo_index).inventory_item_id := p_inventory_item_id;
10570: g_rootinfos(l_rootinfo_index).organization_id := p_organization_id;
10571: END IF;
10572: build_tree(l_return_status, l_rootinfo_index);
10573: IF (l_return_status = fnd_api.g_ret_sts_error OR l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
10574: -- if build_tree failed
10575: -- invalidate the rootinfo record and the index to the record
10576: invalidate_tree(l_rootinfo_index);
10577: l_tree_id := 0;
10576: invalidate_tree(l_rootinfo_index);
10577: l_tree_id := 0;
10578: END IF;
10579:
10580: IF l_return_status = fnd_api.g_ret_sts_error THEN
10581: RAISE fnd_api.g_exc_error;
10582: End IF ;
10583:
10584: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10577: l_tree_id := 0;
10578: END IF;
10579:
10580: IF l_return_status = fnd_api.g_ret_sts_error THEN
10581: RAISE fnd_api.g_exc_error;
10582: End IF ;
10583:
10584: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10585: RAISE fnd_api.g_exc_unexpected_error;
10580: IF l_return_status = fnd_api.g_ret_sts_error THEN
10581: RAISE fnd_api.g_exc_error;
10582: End IF ;
10583:
10584: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10585: RAISE fnd_api.g_exc_unexpected_error;
10586: End IF;
10587:
10588: END IF;
10581: RAISE fnd_api.g_exc_error;
10582: End IF ;
10583:
10584: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10585: RAISE fnd_api.g_exc_unexpected_error;
10586: End IF;
10587:
10588: END IF;
10589:
10603: END IF;
10604:
10605: EXCEPTION
10606:
10607: WHEN fnd_api.g_exc_error THEN
10608: print_debug(' CreateTree ending with g_exc_error.'||SQLERRM,9);
10609: x_return_status := fnd_api.g_ret_sts_error;
10610:
10611: -- Get message count and data
10605: EXCEPTION
10606:
10607: WHEN fnd_api.g_exc_error THEN
10608: print_debug(' CreateTree ending with g_exc_error.'||SQLERRM,9);
10609: x_return_status := fnd_api.g_ret_sts_error;
10610:
10611: -- Get message count and data
10612: fnd_msg_pub.count_and_get
10613: ( p_count => x_msg_count
10613: ( p_count => x_msg_count
10614: , p_data => x_msg_data
10615: );
10616:
10617: WHEN fnd_api.g_exc_unexpected_error THEN
10618: print_debug(' CreateTree ending with g_exc_unexpected_error.'||SQLERRM,9);
10619: x_return_status := fnd_api.g_ret_sts_unexp_error ;
10620:
10621: -- Get message count and data
10615: );
10616:
10617: WHEN fnd_api.g_exc_unexpected_error THEN
10618: print_debug(' CreateTree ending with g_exc_unexpected_error.'||SQLERRM,9);
10619: x_return_status := fnd_api.g_ret_sts_unexp_error ;
10620:
10621: -- Get message count and data
10622: fnd_msg_pub.count_and_get
10623: ( p_count => x_msg_count
10625: );
10626:
10627: WHEN OTHERS THEN
10628: print_debug(' CreateTree ending with OTHERS.'||SQLERRM,9);
10629: x_return_status := fnd_api.g_ret_sts_unexp_error ;
10630:
10631: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
10632: THEN
10633: fnd_msg_pub.add_exc_msg
10841: );
10842: END LOOP;
10843:
10844: -- return status
10845: x_return_status := fnd_api.g_ret_sts_success;
10846: EXCEPTION
10847: WHEN OTHERS THEN
10848: x_return_status := fnd_api.g_ret_sts_unexp_error;
10849:
10844: -- return status
10845: x_return_status := fnd_api.g_ret_sts_success;
10846: EXCEPTION
10847: WHEN OTHERS THEN
10848: x_return_status := fnd_api.g_ret_sts_unexp_error;
10849:
10850: END prepare_reservation_quantities;
10851:
10852: -- Get_Total_QOH
10918: , p_lpn_id IN NUMBER
10919: ) IS
10920:
10921: l_found BOOLEAN;
10922: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
10923: l_node_index NUMBER;
10924: l_tqoh NUMBER;
10925: l_stqoh NUMBER;
10926: l_loop_index NUMBER;
10964: , p_cost_group_id => p_cost_group_id
10965: , p_lpn_id => p_lpn_id
10966: );
10967:
10968: IF l_return_status = fnd_api.g_ret_sts_error THEN
10969: RAISE fnd_api.g_exc_error;
10970: End IF ;
10971:
10972:
10965: , p_lpn_id => p_lpn_id
10966: );
10967:
10968: IF l_return_status = fnd_api.g_ret_sts_error THEN
10969: RAISE fnd_api.g_exc_error;
10970: End IF ;
10971:
10972:
10973: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10969: RAISE fnd_api.g_exc_error;
10970: End IF ;
10971:
10972:
10973: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10974: RAISE fnd_api.g_exc_unexpected_error;
10975: End IF;
10976:
10977:
10970: End IF ;
10971:
10972:
10973: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
10974: RAISE fnd_api.g_exc_unexpected_error;
10975: End IF;
10976:
10977:
10978: IF l_found = FALSE THEN
10978: IF l_found = FALSE THEN
10979: fnd_message.set_name('INV','INV-Cannot find node');
10980: fnd_message.set_token('ROUTINE', 'Find_Tree_Node');
10981: fnd_msg_pub.ADD;
10982: RAISE fnd_api.g_exc_unexpected_error;
10983: End IF;
10984:
10985: print_debug('get_total_qoh: node found... node_index='||l_node_index||' level='||g_nodes(l_node_index).node_level||'<>'||g_item_level);
10986:
11065: END IF;
11066:
11067: EXCEPTION
11068:
11069: WHEN fnd_api.g_exc_error THEN
11070: x_return_status := fnd_api.g_ret_sts_error;
11071:
11072: -- Get message count and data
11073: fnd_msg_pub.count_and_get
11066:
11067: EXCEPTION
11068:
11069: WHEN fnd_api.g_exc_error THEN
11070: x_return_status := fnd_api.g_ret_sts_error;
11071:
11072: -- Get message count and data
11073: fnd_msg_pub.count_and_get
11074: ( p_count => x_msg_count
11073: fnd_msg_pub.count_and_get
11074: ( p_count => x_msg_count
11075: , p_data => x_msg_data);
11076:
11077: WHEN fnd_api.g_exc_unexpected_error THEN
11078: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11079:
11080: -- Get message count and data
11081: fnd_msg_pub.count_and_get
11074: ( p_count => x_msg_count
11075: , p_data => x_msg_data);
11076:
11077: WHEN fnd_api.g_exc_unexpected_error THEN
11078: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11079:
11080: -- Get message count and data
11081: fnd_msg_pub.count_and_get
11082: ( p_count => x_msg_count
11082: ( p_count => x_msg_count
11083: , p_data => x_msg_data);
11084:
11085: WHEN OTHERS THEN
11086: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11087:
11088:
11089: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
11090: THEN
11120: -- x_no_violation true if no violation, false otherwise
11121: --
11122: PROCEDURE do_check_pvt
11123: ( p_api_version_number IN NUMBER
11124: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
11125: , x_return_status OUT NOCOPY VARCHAR2
11126: , x_msg_count OUT NOCOPY NUMBER
11127: , x_msg_data OUT NOCOPY VARCHAR2
11128: , p_tree_id IN INTEGER
11129: , x_no_violation OUT NOCOPY BOOLEAN
11130: ) IS
11131: l_api_version_number CONSTANT NUMBER := 1.0;
11132: l_api_name CONSTANT VARCHAR2(30) := 'Do_Check_Pvt';
11133: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11134: l_no_violation BOOLEAN;
11135: BEGIN
11136: print_debug('Entering do_check_pvt. tree_id='||p_tree_id);
11137:
11135: BEGIN
11136: print_debug('Entering do_check_pvt. tree_id='||p_tree_id);
11137:
11138: -- Standard call to check for call compatibility
11139: IF NOT fnd_api.compatible_api_call(l_api_version_number
11140: , p_api_version_number
11141: , l_api_name
11142: , G_PKG_NAME
11143: ) THEN
11140: , p_api_version_number
11141: , l_api_name
11142: , G_PKG_NAME
11143: ) THEN
11144: RAISE fnd_api.g_exc_unexpected_error;
11145: END IF;
11146:
11147: -- Initialize message list.
11148: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11144: RAISE fnd_api.g_exc_unexpected_error;
11145: END IF;
11146:
11147: -- Initialize message list.
11148: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11149: fnd_msg_pub.initialize;
11150: END IF;
11151:
11152: -- check if tree id is valid
11153: IF is_tree_valid(p_tree_id) = FALSE THEN
11154: fnd_message.set_name('INV', 'INV-Qtyroot not found');
11155: fnd_message.set_token('ROUTINE', 'Do_Check');
11156: fnd_msg_pub.ADD;
11157: RAISE fnd_api.g_exc_unexpected_error;
11158: END IF;
11159:
11160:
11161: zero_tree_node(l_return_status, g_rootinfos(p_tree_id).item_node_index);
11158: END IF;
11159:
11160:
11161: zero_tree_node(l_return_status, g_rootinfos(p_tree_id).item_node_index);
11162: IF l_return_status = fnd_api.g_ret_sts_error THEN
11163: RAISE fnd_api.g_exc_error;
11164: End IF ;
11165:
11166: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11159:
11160:
11161: zero_tree_node(l_return_status, g_rootinfos(p_tree_id).item_node_index);
11162: IF l_return_status = fnd_api.g_ret_sts_error THEN
11163: RAISE fnd_api.g_exc_error;
11164: End IF ;
11165:
11166: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11167: RAISE fnd_api.g_exc_unexpected_error;
11162: IF l_return_status = fnd_api.g_ret_sts_error THEN
11163: RAISE fnd_api.g_exc_error;
11164: End IF ;
11165:
11166: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11167: RAISE fnd_api.g_exc_unexpected_error;
11168: End IF;
11169:
11170: --lock the tree - do_check must acquire tree lock before building tree
11163: RAISE fnd_api.g_exc_error;
11164: End IF ;
11165:
11166: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11167: RAISE fnd_api.g_exc_unexpected_error;
11168: End IF;
11169:
11170: --lock the tree - do_check must acquire tree lock before building tree
11171: print_debug('in do_check_pvt, calling lock_tree ');
11170: --lock the tree - do_check must acquire tree lock before building tree
11171: print_debug('in do_check_pvt, calling lock_tree ');
11172: lock_tree(
11173: p_api_version_number => 1.0
11174: , p_init_msg_lst => fnd_api.g_false
11175: , x_return_status => l_return_status
11176: , x_msg_count => x_msg_count
11177: , x_msg_data => x_msg_data
11178: , p_organization_id => g_rootinfos(p_tree_id).organization_id
11178: , p_organization_id => g_rootinfos(p_tree_id).organization_id
11179: , p_inventory_item_id => g_rootinfos(p_tree_id).inventory_item_id
11180: );
11181:
11182: IF l_return_status = fnd_api.g_ret_sts_error THEN
11183: RAISE fnd_api.g_exc_error;
11184: END IF ;
11185:
11186: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11179: , p_inventory_item_id => g_rootinfos(p_tree_id).inventory_item_id
11180: );
11181:
11182: IF l_return_status = fnd_api.g_ret_sts_error THEN
11183: RAISE fnd_api.g_exc_error;
11184: END IF ;
11185:
11186: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11187: RAISE fnd_api.g_exc_unexpected_error;
11182: IF l_return_status = fnd_api.g_ret_sts_error THEN
11183: RAISE fnd_api.g_exc_error;
11184: END IF ;
11185:
11186: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11187: RAISE fnd_api.g_exc_unexpected_error;
11188: END IF;
11189:
11190: print_debug('in do_check_pvt, calling build_tree for tree_id='||p_tree_id);
11183: RAISE fnd_api.g_exc_error;
11184: END IF ;
11185:
11186: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11187: RAISE fnd_api.g_exc_unexpected_error;
11188: END IF;
11189:
11190: print_debug('in do_check_pvt, calling build_tree for tree_id='||p_tree_id);
11191: build_tree(l_return_status, p_tree_id);
11189:
11190: print_debug('in do_check_pvt, calling build_tree for tree_id='||p_tree_id);
11191: build_tree(l_return_status, p_tree_id);
11192: print_debug('in do_check_pvt, after build_tree... return='||l_return_status);
11193: IF l_return_status = fnd_api.g_ret_sts_error THEN
11194: RAISE fnd_api.g_exc_error;
11195: End IF ;
11196:
11197: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11190: print_debug('in do_check_pvt, calling build_tree for tree_id='||p_tree_id);
11191: build_tree(l_return_status, p_tree_id);
11192: print_debug('in do_check_pvt, after build_tree... return='||l_return_status);
11193: IF l_return_status = fnd_api.g_ret_sts_error THEN
11194: RAISE fnd_api.g_exc_error;
11195: End IF ;
11196:
11197: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11198: RAISE fnd_api.g_exc_unexpected_error;
11193: IF l_return_status = fnd_api.g_ret_sts_error THEN
11194: RAISE fnd_api.g_exc_error;
11195: End IF ;
11196:
11197: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11198: RAISE fnd_api.g_exc_unexpected_error;
11199: End IF;
11200:
11201: if g_rootinfos(p_tree_id).neg_inv_allowed
11194: RAISE fnd_api.g_exc_error;
11195: End IF ;
11196:
11197: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11198: RAISE fnd_api.g_exc_unexpected_error;
11199: End IF;
11200:
11201: if g_rootinfos(p_tree_id).neg_inv_allowed
11202: then
11215: , p_item_node_index => g_rootinfos(p_tree_id).item_node_index
11216: );
11217:
11218: print_debug('in do_check_pvt, after check_node_violation... return='||l_return_status);
11219: IF l_return_status = fnd_api.g_ret_sts_error THEN
11220: RAISE fnd_api.g_exc_error;
11221: END IF ;
11222:
11223: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11216: );
11217:
11218: print_debug('in do_check_pvt, after check_node_violation... return='||l_return_status);
11219: IF l_return_status = fnd_api.g_ret_sts_error THEN
11220: RAISE fnd_api.g_exc_error;
11221: END IF ;
11222:
11223: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11224: RAISE fnd_api.g_exc_unexpected_error;
11219: IF l_return_status = fnd_api.g_ret_sts_error THEN
11220: RAISE fnd_api.g_exc_error;
11221: END IF ;
11222:
11223: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11224: RAISE fnd_api.g_exc_unexpected_error;
11225: END IF;
11226:
11227: x_no_violation := l_no_violation;
11220: RAISE fnd_api.g_exc_error;
11221: END IF ;
11222:
11223: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11224: RAISE fnd_api.g_exc_unexpected_error;
11225: END IF;
11226:
11227: x_no_violation := l_no_violation;
11228: x_return_status := l_return_status;
11229:
11230: print_debug('Normal end of do_check_pvt');
11231: EXCEPTION
11232:
11233: WHEN fnd_api.g_exc_error THEN
11234: x_return_status := fnd_api.g_ret_sts_error;
11235:
11236: -- Get message count and data
11237: fnd_msg_pub.count_and_get
11230: print_debug('Normal end of do_check_pvt');
11231: EXCEPTION
11232:
11233: WHEN fnd_api.g_exc_error THEN
11234: x_return_status := fnd_api.g_ret_sts_error;
11235:
11236: -- Get message count and data
11237: fnd_msg_pub.count_and_get
11238: ( p_count => x_msg_count
11238: ( p_count => x_msg_count
11239: , p_data => x_msg_data
11240: );
11241:
11242: WHEN fnd_api.g_exc_unexpected_error THEN
11243: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11244:
11245: -- Get message count and data
11246: fnd_msg_pub.count_and_get
11239: , p_data => x_msg_data
11240: );
11241:
11242: WHEN fnd_api.g_exc_unexpected_error THEN
11243: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11244:
11245: -- Get message count and data
11246: fnd_msg_pub.count_and_get
11247: ( p_count => x_msg_count
11248: , p_data => x_msg_data
11249: );
11250:
11251: WHEN OTHERS THEN
11252: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11253:
11254: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
11255: THEN
11256: fnd_msg_pub.add_exc_msg
11289: -- x_no_violation true if no violation, false otherwise
11290: --
11291: PROCEDURE do_check
11292: ( p_api_version_number IN NUMBER
11293: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
11294: , x_return_status OUT NOCOPY VARCHAR2
11295: , x_msg_count OUT NOCOPY NUMBER
11296: , x_msg_data OUT NOCOPY VARCHAR2
11297: , p_tree_id IN INTEGER
11298: , x_no_violation OUT NOCOPY BOOLEAN
11299: ) IS
11300: l_api_version_number CONSTANT NUMBER := 1.0;
11301: l_api_name CONSTANT VARCHAR2(30) := 'DO_CHECK';
11302: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11303: l_no_violation BOOLEAN;
11304: l_root_id INTEGER;
11305: BEGIN
11306: IF g_debug = 1 THEN
11309:
11310: print_debug('Entering do_check, tree_id='||p_tree_id, 9);
11311:
11312: -- Standard call to check for call compatibility
11313: IF NOT fnd_api.compatible_api_call(l_api_version_number
11314: , p_api_version_number
11315: , l_api_name
11316: , G_PKG_NAME
11317: ) THEN
11314: , p_api_version_number
11315: , l_api_name
11316: , G_PKG_NAME
11317: ) THEN
11318: RAISE fnd_api.g_exc_unexpected_error;
11319: END IF;
11320:
11321: -- Initialize message list.
11322: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11318: RAISE fnd_api.g_exc_unexpected_error;
11319: END IF;
11320:
11321: -- Initialize message list.
11322: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11323: fnd_msg_pub.initialize;
11324: END IF;
11325:
11326: --call do_check on the base tree
11327: l_root_id := g_demand_info(p_tree_id).root_id;
11328:
11329: do_check_pvt(
11330: p_api_version_number => 1.0
11331: , p_init_msg_lst => fnd_api.g_false
11332: , x_return_status => l_return_status
11333: , x_msg_count => x_msg_count
11334: , x_msg_data => x_msg_data
11335: , p_tree_id => l_root_id
11335: , p_tree_id => l_root_id
11336: , x_no_violation => l_no_violation
11337: );
11338: print_debug('In do_check, after do_check_pvt, return='||l_return_status);
11339: IF l_return_status = fnd_api.g_ret_sts_error THEN
11340: RAISE fnd_api.g_exc_error;
11341: END IF ;
11342:
11343: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11336: , x_no_violation => l_no_violation
11337: );
11338: print_debug('In do_check, after do_check_pvt, return='||l_return_status);
11339: IF l_return_status = fnd_api.g_ret_sts_error THEN
11340: RAISE fnd_api.g_exc_error;
11341: END IF ;
11342:
11343: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11344: RAISE fnd_api.g_exc_unexpected_error;
11339: IF l_return_status = fnd_api.g_ret_sts_error THEN
11340: RAISE fnd_api.g_exc_error;
11341: END IF ;
11342:
11343: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11344: RAISE fnd_api.g_exc_unexpected_error;
11345: END IF;
11346:
11347: -- Bug 7340567, printing the tree to verify node violation
11340: RAISE fnd_api.g_exc_error;
11341: END IF ;
11342:
11343: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11344: RAISE fnd_api.g_exc_unexpected_error;
11345: END IF;
11346:
11347: -- Bug 7340567, printing the tree to verify node violation
11348: print_tree(p_tree_id);
11359: print_debug(' ',9);
11360: END IF;
11361: EXCEPTION
11362:
11363: WHEN fnd_api.g_exc_error THEN
11364: x_return_status := fnd_api.g_ret_sts_error;
11365:
11366: -- Get message count and data
11367: fnd_msg_pub.count_and_get
11360: END IF;
11361: EXCEPTION
11362:
11363: WHEN fnd_api.g_exc_error THEN
11364: x_return_status := fnd_api.g_ret_sts_error;
11365:
11366: -- Get message count and data
11367: fnd_msg_pub.count_and_get
11368: ( p_count => x_msg_count
11368: ( p_count => x_msg_count
11369: , p_data => x_msg_data
11370: );
11371:
11372: WHEN fnd_api.g_exc_unexpected_error THEN
11373: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11374:
11375: -- Get message count and data
11376: fnd_msg_pub.count_and_get
11369: , p_data => x_msg_data
11370: );
11371:
11372: WHEN fnd_api.g_exc_unexpected_error THEN
11373: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11374:
11375: -- Get message count and data
11376: fnd_msg_pub.count_and_get
11377: ( p_count => x_msg_count
11378: , p_data => x_msg_data
11379: );
11380:
11381: WHEN OTHERS THEN
11382: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11383:
11384: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
11385: THEN
11386: fnd_msg_pub.add_exc_msg
11427: , x_no_violation OUT NOCOPY BOOLEAN
11428: ) IS
11429: l_api_version_number CONSTANT NUMBER := 1.0;
11430: l_api_name CONSTANT VARCHAR2(30) := 'DO_CHECK_ALL';
11431: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11432: l_no_violation BOOLEAN;
11433: l_root_id NUMBER;
11434: l_org_id NUMBER;
11435: l_item_id NUMBER;
11447: print_debug(l_api_name || ' Entered',9);
11448: END IF;
11449:
11450: -- Standard call to check for call compatibility
11451: IF NOT fnd_api.compatible_api_call(l_api_version_number
11452: , p_api_version_number
11453: , l_api_name
11454: , G_PKG_NAME
11455: ) THEN
11452: , p_api_version_number
11453: , l_api_name
11454: , G_PKG_NAME
11455: ) THEN
11456: RAISE fnd_api.g_exc_unexpected_error;
11457: END IF;
11458:
11459: -- Initialize message list.
11460: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11456: RAISE fnd_api.g_exc_unexpected_error;
11457: END IF;
11458:
11459: -- Initialize message list.
11460: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11461: fnd_msg_pub.initialize;
11462: END IF;
11463:
11464: for l_loop_index IN 1..g_all_roots_counter LOOP
11493: EXIT WHEN c1%NOTFOUND;
11494:
11495: IF is_tree_valid(l_root_id_tmp) THEN
11496: do_check_pvt( p_api_version_number => 1.0
11497: ,p_init_msg_lst => fnd_api.g_false
11498: ,x_return_status => l_return_status
11499: ,x_msg_count => x_msg_count
11500: ,x_msg_data => x_msg_data
11501: ,p_tree_id => l_root_id_tmp
11499: ,x_msg_count => x_msg_count
11500: ,x_msg_data => x_msg_data
11501: ,p_tree_id => l_root_id_tmp
11502: ,x_no_violation => l_no_violation);
11503: IF l_return_status = fnd_api.g_ret_sts_error THEN
11504: RAISE fnd_api.g_exc_error;
11505: END IF ;
11506:
11507: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11500: ,x_msg_data => x_msg_data
11501: ,p_tree_id => l_root_id_tmp
11502: ,x_no_violation => l_no_violation);
11503: IF l_return_status = fnd_api.g_ret_sts_error THEN
11504: RAISE fnd_api.g_exc_error;
11505: END IF ;
11506:
11507: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11508: RAISE fnd_api.g_exc_unexpected_error;
11503: IF l_return_status = fnd_api.g_ret_sts_error THEN
11504: RAISE fnd_api.g_exc_error;
11505: END IF ;
11506:
11507: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11508: RAISE fnd_api.g_exc_unexpected_error;
11509: END IF;
11510:
11511: IF l_no_violation = FALSE THEN
11504: RAISE fnd_api.g_exc_error;
11505: END IF ;
11506:
11507: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11508: RAISE fnd_api.g_exc_unexpected_error;
11509: END IF;
11510:
11511: IF l_no_violation = FALSE THEN
11512: --utl_debug1('do_check_pvt returns false to do_check');
11523: -- IF is_tree_valid(l_root_id) THEN
11524: -- do_check_pvt
11525: -- (
11526: -- p_api_version_number => 1.0
11527: -- , p_init_msg_lst => fnd_api.g_false
11528: -- , x_return_status => l_return_status
11529: -- , x_msg_count => x_msg_count
11530: -- , x_msg_data => x_msg_data
11531: -- , p_tree_id => l_root_id
11530: -- , x_msg_data => x_msg_data
11531: -- , p_tree_id => l_root_id
11532: -- , x_no_violation => l_no_violation
11533: -- );
11534: -- IF l_return_status = fnd_api.g_ret_sts_error THEN
11535: -- RAISE fnd_api.g_exc_error;
11536: -- END IF ;
11537: --
11538: -- IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11531: -- , p_tree_id => l_root_id
11532: -- , x_no_violation => l_no_violation
11533: -- );
11534: -- IF l_return_status = fnd_api.g_ret_sts_error THEN
11535: -- RAISE fnd_api.g_exc_error;
11536: -- END IF ;
11537: --
11538: -- IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11539: -- RAISE fnd_api.g_exc_unexpected_error;
11534: -- IF l_return_status = fnd_api.g_ret_sts_error THEN
11535: -- RAISE fnd_api.g_exc_error;
11536: -- END IF ;
11537: --
11538: -- IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11539: -- RAISE fnd_api.g_exc_unexpected_error;
11540: -- END IF;
11541: --
11542: -- IF l_no_violation = FALSE THEN
11535: -- RAISE fnd_api.g_exc_error;
11536: -- END IF ;
11537: --
11538: -- IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11539: -- RAISE fnd_api.g_exc_unexpected_error;
11540: -- END IF;
11541: --
11542: -- IF l_no_violation = FALSE THEN
11543: -- utl_debug1('do_check_pvt returns false to do_check');
11560: END IF;
11561:
11562: EXCEPTION
11563:
11564: WHEN fnd_api.g_exc_error THEN
11565: x_return_status := fnd_api.g_ret_sts_error;
11566:
11567: -- Get message count and data
11568: fnd_msg_pub.count_and_get
11561:
11562: EXCEPTION
11563:
11564: WHEN fnd_api.g_exc_error THEN
11565: x_return_status := fnd_api.g_ret_sts_error;
11566:
11567: -- Get message count and data
11568: fnd_msg_pub.count_and_get
11569: ( p_count => x_msg_count
11569: ( p_count => x_msg_count
11570: , p_data => x_msg_data
11571: );
11572:
11573: WHEN fnd_api.g_exc_unexpected_error THEN
11574: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11575:
11576: -- Get message count and data
11577: fnd_msg_pub.count_and_get
11570: , p_data => x_msg_data
11571: );
11572:
11573: WHEN fnd_api.g_exc_unexpected_error THEN
11574: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11575:
11576: -- Get message count and data
11577: fnd_msg_pub.count_and_get
11578: ( p_count => x_msg_count
11579: , p_data => x_msg_data
11580: );
11581:
11582: WHEN OTHERS THEN
11583: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11584:
11585: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
11586: THEN
11587: fnd_msg_pub.add_exc_msg
11620: -- x_msg_data standard output parameter
11621: --
11622: PROCEDURE free_tree_pvt
11623: ( p_api_version_number IN NUMBER
11624: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
11625: , x_return_status OUT NOCOPY VARCHAR2
11626: , x_msg_count OUT NOCOPY NUMBER
11627: , x_msg_data OUT NOCOPY VARCHAR2
11628: , p_tree_id IN INTEGER
11628: , p_tree_id IN INTEGER
11629: ) IS
11630: l_api_version_number CONSTANT NUMBER := 1.0;
11631: l_api_name CONSTANT VARCHAR2(30) := 'Free_Tree_Pvt';
11632: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11633: BEGIN
11634:
11635: -- Standard call to check for call compatibility
11636: IF NOT fnd_api.compatible_api_call(l_api_version_number
11632: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11633: BEGIN
11634:
11635: -- Standard call to check for call compatibility
11636: IF NOT fnd_api.compatible_api_call(l_api_version_number
11637: , p_api_version_number
11638: , l_api_name
11639: , G_PKG_NAME
11640: ) THEN
11637: , p_api_version_number
11638: , l_api_name
11639: , G_PKG_NAME
11640: ) THEN
11641: RAISE fnd_api.g_exc_unexpected_error;
11642: END IF;
11643:
11644: -- Initialize message list.
11645: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11641: RAISE fnd_api.g_exc_unexpected_error;
11642: END IF;
11643:
11644: -- Initialize message list.
11645: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11646: fnd_msg_pub.initialize;
11647: END IF;
11648: /* This code was causing bug. As soon as one invalid tree
11649: * was reached, no other trees were freed.
11653: *IF is_tree_valid(p_tree_id) = FALSE THEN
11654: * fnd_message.set_name('INV', 'INV-Qtyroot not found');
11655: * fnd_message.set_token('ROUTINE', 'Free_Tree');
11656: * fnd_msg_pub.ADD;
11657: * RAISE fnd_api.g_exc_unexpected_error;
11658: *END IF;
11659: */
11660: invalidate_tree(p_tree_id);
11661:
11662: x_return_status := l_return_status;
11663:
11664: EXCEPTION
11665:
11666: WHEN fnd_api.g_exc_error THEN
11667: x_return_status := fnd_api.g_ret_sts_error;
11668:
11669: -- Get message count and data
11670: fnd_msg_pub.count_and_get
11663:
11664: EXCEPTION
11665:
11666: WHEN fnd_api.g_exc_error THEN
11667: x_return_status := fnd_api.g_ret_sts_error;
11668:
11669: -- Get message count and data
11670: fnd_msg_pub.count_and_get
11671: ( p_count => x_msg_count
11671: ( p_count => x_msg_count
11672: , p_data => x_msg_data
11673: );
11674:
11675: WHEN fnd_api.g_exc_unexpected_error THEN
11676: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11677:
11678: -- Get message count and data
11679: fnd_msg_pub.count_and_get
11672: , p_data => x_msg_data
11673: );
11674:
11675: WHEN fnd_api.g_exc_unexpected_error THEN
11676: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11677:
11678: -- Get message count and data
11679: fnd_msg_pub.count_and_get
11680: ( p_count => x_msg_count
11681: , p_data => x_msg_data
11682: );
11683:
11684: WHEN OTHERS THEN
11685: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11686:
11687: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
11688: THEN
11689: fnd_msg_pub.add_exc_msg
11733: IS
11734:
11735: l_api_version_number CONSTANT NUMBER := 1.0;
11736: l_api_name CONSTANT VARCHAR2(30) := 'FREE_TREE';
11737: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11738: l_root_id INTEGER;
11739:
11740: BEGIN
11741:
11743: print_debug(l_api_name || ' Entered',9);
11744: END IF;
11745:
11746: -- Standard call to check for call compatibility
11747: IF NOT fnd_api.compatible_api_call(l_api_version_number
11748: , p_api_version_number
11749: , l_api_name
11750: , G_PKG_NAME
11751: ) THEN
11748: , p_api_version_number
11749: , l_api_name
11750: , G_PKG_NAME
11751: ) THEN
11752: RAISE fnd_api.g_exc_unexpected_error;
11753: END IF;
11754:
11755: -- Initialize message list.
11756: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11752: RAISE fnd_api.g_exc_unexpected_error;
11753: END IF;
11754:
11755: -- Initialize message list.
11756: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11757: fnd_msg_pub.initialize;
11758: END IF;
11759:
11760: --call free_tree on base_tree
11761: l_root_id := g_demand_info(p_tree_id).root_id;
11762:
11763: free_tree_pvt(
11764: p_api_version_number => l_api_version_number
11765: , p_init_msg_lst => fnd_api.g_false
11766: , x_return_status => l_return_status
11767: , x_msg_count => x_msg_count
11768: , x_msg_data => x_msg_data
11769: , p_tree_id => l_root_id
11768: , x_msg_data => x_msg_data
11769: , p_tree_id => l_root_id
11770: );
11771:
11772: IF l_return_status = fnd_api.g_ret_sts_error THEN
11773: RAISE fnd_api.g_exc_error;
11774: End IF ;
11775:
11776: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11769: , p_tree_id => l_root_id
11770: );
11771:
11772: IF l_return_status = fnd_api.g_ret_sts_error THEN
11773: RAISE fnd_api.g_exc_error;
11774: End IF ;
11775:
11776: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11777: RAISE fnd_api.g_exc_unexpected_error;
11772: IF l_return_status = fnd_api.g_ret_sts_error THEN
11773: RAISE fnd_api.g_exc_error;
11774: End IF ;
11775:
11776: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11777: RAISE fnd_api.g_exc_unexpected_error;
11778: End IF;
11779:
11780:
11773: RAISE fnd_api.g_exc_error;
11774: End IF ;
11775:
11776: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11777: RAISE fnd_api.g_exc_unexpected_error;
11778: End IF;
11779:
11780:
11781: x_return_status := l_return_status;
11781: x_return_status := l_return_status;
11782:
11783: EXCEPTION
11784:
11785: WHEN fnd_api.g_exc_error THEN
11786: x_return_status := fnd_api.g_ret_sts_error;
11787:
11788: -- Get message count and data
11789: fnd_msg_pub.count_and_get
11782:
11783: EXCEPTION
11784:
11785: WHEN fnd_api.g_exc_error THEN
11786: x_return_status := fnd_api.g_ret_sts_error;
11787:
11788: -- Get message count and data
11789: fnd_msg_pub.count_and_get
11790: ( p_count => x_msg_count
11790: ( p_count => x_msg_count
11791: , p_data => x_msg_data
11792: );
11793:
11794: WHEN fnd_api.g_exc_unexpected_error THEN
11795: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11796:
11797: -- Get message count and data
11798: fnd_msg_pub.count_and_get
11791: , p_data => x_msg_data
11792: );
11793:
11794: WHEN fnd_api.g_exc_unexpected_error THEN
11795: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11796:
11797: -- Get message count and data
11798: fnd_msg_pub.count_and_get
11799: ( p_count => x_msg_count
11800: , p_data => x_msg_data
11801: );
11802:
11803: WHEN OTHERS THEN
11804: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11805:
11806: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
11807: THEN
11808: fnd_msg_pub.add_exc_msg
11849: IS
11850:
11851: l_api_version_number CONSTANT NUMBER := 1.0;
11852: l_api_name CONSTANT VARCHAR2(30) := 'Free_All';
11853: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11854: l_root_id NUMBER;
11855:
11856: BEGIN
11857:
11855:
11856: BEGIN
11857:
11858: -- Standard call to check for call compatibility
11859: IF NOT fnd_api.compatible_api_call(l_api_version_number
11860: , p_api_version_number
11861: , l_api_name
11862: , G_PKG_NAME
11863: ) THEN
11860: , p_api_version_number
11861: , l_api_name
11862: , G_PKG_NAME
11863: ) THEN
11864: RAISE fnd_api.g_exc_unexpected_error;
11865: END IF;
11866:
11867: -- Initialize message list.
11868: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11864: RAISE fnd_api.g_exc_unexpected_error;
11865: END IF;
11866:
11867: -- Initialize message list.
11868: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11869: fnd_msg_pub.initialize;
11870: END IF;
11871:
11872: FOR l_loop_index IN 1..g_all_roots_counter LOOP
11872: FOR l_loop_index IN 1..g_all_roots_counter LOOP
11873: l_root_id := g_all_roots(l_loop_index).root_id;
11874: free_tree_pvt(
11875: p_api_version_number => l_api_version_number
11876: ,p_init_msg_lst => fnd_api.g_false
11877: ,x_return_status => l_return_status
11878: ,x_msg_count => x_msg_count
11879: ,x_msg_data => x_msg_data
11880: ,p_tree_id => l_root_id
11879: ,x_msg_data => x_msg_data
11880: ,p_tree_id => l_root_id
11881: );
11882:
11883: IF l_return_status = fnd_api.g_ret_sts_error THEN
11884: RAISE fnd_api.g_exc_error;
11885: End IF ;
11886:
11887: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11880: ,p_tree_id => l_root_id
11881: );
11882:
11883: IF l_return_status = fnd_api.g_ret_sts_error THEN
11884: RAISE fnd_api.g_exc_error;
11885: End IF ;
11886:
11887: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11888: RAISE fnd_api.g_exc_unexpected_error;
11883: IF l_return_status = fnd_api.g_ret_sts_error THEN
11884: RAISE fnd_api.g_exc_error;
11885: End IF ;
11886:
11887: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11888: RAISE fnd_api.g_exc_unexpected_error;
11889: End IF;
11890:
11891: END LOOP;
11884: RAISE fnd_api.g_exc_error;
11885: End IF ;
11886:
11887: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
11888: RAISE fnd_api.g_exc_unexpected_error;
11889: End IF;
11890:
11891: END LOOP;
11892:
11893: x_return_status := l_return_status;
11894:
11895: EXCEPTION
11896:
11897: WHEN fnd_api.g_exc_error THEN
11898: x_return_status := fnd_api.g_ret_sts_error;
11899:
11900: -- Get message count and data
11901: fnd_msg_pub.count_and_get
11894:
11895: EXCEPTION
11896:
11897: WHEN fnd_api.g_exc_error THEN
11898: x_return_status := fnd_api.g_ret_sts_error;
11899:
11900: -- Get message count and data
11901: fnd_msg_pub.count_and_get
11902: ( p_count => x_msg_count
11902: ( p_count => x_msg_count
11903: , p_data => x_msg_data
11904: );
11905:
11906: WHEN fnd_api.g_exc_unexpected_error THEN
11907: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11908:
11909: -- Get message count and data
11910: fnd_msg_pub.count_and_get
11903: , p_data => x_msg_data
11904: );
11905:
11906: WHEN fnd_api.g_exc_unexpected_error THEN
11907: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11908:
11909: -- Get message count and data
11910: fnd_msg_pub.count_and_get
11911: ( p_count => x_msg_count
11912: , p_data => x_msg_data
11913: );
11914:
11915: WHEN OTHERS THEN
11916: x_return_status := fnd_api.g_ret_sts_unexp_error ;
11917:
11918: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
11919: THEN
11920: fnd_msg_pub.add_exc_msg
11967: ) IS
11968:
11969: l_api_version_number CONSTANT NUMBER := 1.0;
11970: l_api_name CONSTANT VARCHAR2(30) := 'Mark_All_For_Refresh';
11971: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11972: l_root_id NUMBER;
11973:
11974: BEGIN
11975:
11973:
11974: BEGIN
11975:
11976: -- Standard call to check for call compatibility
11977: IF NOT fnd_api.compatible_api_call(l_api_version_number
11978: , p_api_version_number
11979: , l_api_name
11980: , G_PKG_NAME
11981: ) THEN
11978: , p_api_version_number
11979: , l_api_name
11980: , G_PKG_NAME
11981: ) THEN
11982: RAISE fnd_api.g_exc_unexpected_error;
11983: END IF;
11984:
11985: -- Initialize message list.
11986: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11982: RAISE fnd_api.g_exc_unexpected_error;
11983: END IF;
11984:
11985: -- Initialize message list.
11986: IF fnd_api.to_boolean(p_init_msg_lst) THEN
11987: fnd_msg_pub.initialize;
11988: END IF;
11989:
11990: FOR l_loop_index IN 1..g_all_roots_counter LOOP
11996:
11997:
11998: EXCEPTION
11999:
12000: WHEN fnd_api.g_exc_error THEN
12001: x_return_status := fnd_api.g_ret_sts_error;
12002:
12003: -- Get message count and data
12004: fnd_msg_pub.count_and_get
11997:
11998: EXCEPTION
11999:
12000: WHEN fnd_api.g_exc_error THEN
12001: x_return_status := fnd_api.g_ret_sts_error;
12002:
12003: -- Get message count and data
12004: fnd_msg_pub.count_and_get
12005: ( p_count => x_msg_count
12005: ( p_count => x_msg_count
12006: , p_data => x_msg_data
12007: );
12008:
12009: WHEN fnd_api.g_exc_unexpected_error THEN
12010: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12011:
12012: -- Get message count and data
12013: fnd_msg_pub.count_and_get
12006: , p_data => x_msg_data
12007: );
12008:
12009: WHEN fnd_api.g_exc_unexpected_error THEN
12010: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12011:
12012: -- Get message count and data
12013: fnd_msg_pub.count_and_get
12014: ( p_count => x_msg_count
12015: , p_data => x_msg_data
12016: );
12017:
12018: WHEN OTHERS THEN
12019: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12020:
12021: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
12022: THEN
12023: fnd_msg_pub.add_exc_msg
12170: , p_transfer_locator_id IN NUMBER
12171: ) IS
12172: l_api_version_number CONSTANT NUMBER := 1.0;
12173: l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_QUANTITIES';
12174: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
12175: l_root_id INTEGER;
12176: BEGIN
12177: IF g_debug = 1 THEN
12178: print_debug(l_api_name || ' Entered',9);
12180:
12181: print_debug('Entering update_quantities. primQty='||p_primary_quantity||', secQty='||p_secondary_quantity);
12182:
12183: -- Standard call to check for call compatibility
12184: IF NOT fnd_api.compatible_api_call(l_api_version_number
12185: , p_api_version_number
12186: , l_api_name
12187: , G_PKG_NAME
12188: ) THEN
12185: , p_api_version_number
12186: , l_api_name
12187: , G_PKG_NAME
12188: ) THEN
12189: RAISE fnd_api.g_exc_unexpected_error;
12190: END IF;
12191:
12192: -- Initialize message list.
12193: IF fnd_api.to_boolean(p_init_msg_lst) THEN
12189: RAISE fnd_api.g_exc_unexpected_error;
12190: END IF;
12191:
12192: -- Initialize message list.
12193: IF fnd_api.to_boolean(p_init_msg_lst) THEN
12194: fnd_msg_pub.initialize;
12195: END IF;
12196:
12197: l_root_id := g_demand_info(p_tree_id).root_id;
12199: IF is_tree_valid(l_root_id) = FALSE THEN
12200: fnd_message.set_name('INV', 'INV-Qtyroot not found');
12201: fnd_message.set_token('ROUTINE', 'Update_Quantities');
12202: fnd_msg_pub.ADD;
12203: RAISE fnd_api.g_exc_unexpected_error;
12204: END IF;
12205:
12206: print_debug('in update_quantities, calling add_quantities qty1='||p_primary_quantity||', qty2='||p_secondary_quantity||'.');
12207: add_quantities(
12219: , p_cost_group_id => p_cost_group_id
12220: , p_lpn_id => p_lpn_id
12221: );
12222:
12223: IF l_return_status = fnd_api.g_ret_sts_error THEN
12224: RAISE fnd_api.g_exc_error;
12225: End IF ;
12226:
12227: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12220: , p_lpn_id => p_lpn_id
12221: );
12222:
12223: IF l_return_status = fnd_api.g_ret_sts_error THEN
12224: RAISE fnd_api.g_exc_error;
12225: End IF ;
12226:
12227: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12228: RAISE fnd_api.g_exc_unexpected_error;
12223: IF l_return_status = fnd_api.g_ret_sts_error THEN
12224: RAISE fnd_api.g_exc_error;
12225: End IF ;
12226:
12227: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12228: RAISE fnd_api.g_exc_unexpected_error;
12229: End IF;
12230:
12231: -- query the quantities after the update
12224: RAISE fnd_api.g_exc_error;
12225: End IF ;
12226:
12227: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12228: RAISE fnd_api.g_exc_unexpected_error;
12229: End IF;
12230:
12231: -- query the quantities after the update
12232: query_tree
12231: -- query the quantities after the update
12232: query_tree
12233: (
12234: p_api_version_number => 1.0
12235: , p_init_msg_lst => fnd_api.g_false
12236: , x_return_status => l_return_status
12237: , x_msg_count => x_msg_count
12238: , x_msg_data => x_msg_data
12239: , p_tree_id => p_tree_id
12258: , p_lpn_id => p_lpn_id
12259: , p_transfer_locator_id => p_transfer_locator_id
12260: );
12261:
12262: IF l_return_status = fnd_api.g_ret_sts_error THEN
12263: RAISE fnd_api.g_exc_error;
12264: End IF ;
12265:
12266: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12259: , p_transfer_locator_id => p_transfer_locator_id
12260: );
12261:
12262: IF l_return_status = fnd_api.g_ret_sts_error THEN
12263: RAISE fnd_api.g_exc_error;
12264: End IF ;
12265:
12266: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12267: RAISE fnd_api.g_exc_unexpected_error;
12262: IF l_return_status = fnd_api.g_ret_sts_error THEN
12263: RAISE fnd_api.g_exc_error;
12264: End IF ;
12265:
12266: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12267: RAISE fnd_api.g_exc_unexpected_error;
12268: End IF;
12269:
12270: x_return_status := l_return_status;
12263: RAISE fnd_api.g_exc_error;
12264: End IF ;
12265:
12266: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12267: RAISE fnd_api.g_exc_unexpected_error;
12268: End IF;
12269:
12270: x_return_status := l_return_status;
12271:
12275: END IF;
12276:
12277: EXCEPTION
12278:
12279: WHEN fnd_api.g_exc_error THEN
12280: x_return_status := fnd_api.g_ret_sts_error;
12281:
12282: -- Get message count and data
12283: fnd_msg_pub.count_and_get
12276:
12277: EXCEPTION
12278:
12279: WHEN fnd_api.g_exc_error THEN
12280: x_return_status := fnd_api.g_ret_sts_error;
12281:
12282: -- Get message count and data
12283: fnd_msg_pub.count_and_get
12284: ( p_count => x_msg_count
12284: ( p_count => x_msg_count
12285: , p_data => x_msg_data
12286: );
12287:
12288: WHEN fnd_api.g_exc_unexpected_error THEN
12289: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12290:
12291: -- Get message count and data
12292: fnd_msg_pub.count_and_get
12285: , p_data => x_msg_data
12286: );
12287:
12288: WHEN fnd_api.g_exc_unexpected_error THEN
12289: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12290:
12291: -- Get message count and data
12292: fnd_msg_pub.count_and_get
12293: ( p_count => x_msg_count
12294: , p_data => x_msg_data
12295: );
12296:
12297: WHEN OTHERS THEN
12298: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12299:
12300: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
12301: THEN
12302: fnd_msg_pub.add_exc_msg
12455: , p_call_for_form IN VARCHAR2
12456: ) IS
12457: l_api_version_number CONSTANT NUMBER := 1.0;
12458: l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_QUANTITIES_FOR_FORM';
12459: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
12460: l_root_id INTEGER;
12461: l_is_for_form BOOLEAN;
12462: BEGIN
12463:
12465: print_debug(l_api_name || ' Entered',9);
12466: END IF;
12467:
12468: -- Standard call to check for call compatibility
12469: IF NOT fnd_api.compatible_api_call(l_api_version_number
12470: , p_api_version_number
12471: , l_api_name
12472: , G_PKG_NAME
12473: ) THEN
12470: , p_api_version_number
12471: , l_api_name
12472: , G_PKG_NAME
12473: ) THEN
12474: RAISE fnd_api.g_exc_unexpected_error;
12475: END IF;
12476:
12477: -- Initialize message list.
12478: IF fnd_api.to_boolean(p_init_msg_lst) THEN
12474: RAISE fnd_api.g_exc_unexpected_error;
12475: END IF;
12476:
12477: -- Initialize message list.
12478: IF fnd_api.to_boolean(p_init_msg_lst) THEN
12479: fnd_msg_pub.initialize;
12480: END IF;
12481:
12482: l_root_id := g_demand_info(p_tree_id).root_id;
12485: IF is_tree_valid(l_root_id) = FALSE THEN
12486: fnd_message.set_name('INV', 'INV-Qtyroot not found');
12487: fnd_message.set_token('ROUTINE', 'Update_Quantities');
12488: fnd_msg_pub.ADD;
12489: RAISE fnd_api.g_exc_unexpected_error;
12490: END IF;
12491:
12492: IF p_call_for_form = fnd_api.g_true THEN
12493: l_is_for_form := FALSE;
12488: fnd_msg_pub.ADD;
12489: RAISE fnd_api.g_exc_unexpected_error;
12490: END IF;
12491:
12492: IF p_call_for_form = fnd_api.g_true THEN
12493: l_is_for_form := FALSE;
12494: ELSE
12495: l_is_for_form := TRUE;
12496: END IF;
12509: , p_quantity_type => p_quantity_type
12510: , p_set_check_mark => l_is_for_form
12511: , p_cost_group_id => p_cost_group_id
12512: , p_lpn_id => NULL
12513: , p_check_mark_node_only => fnd_api.g_true
12514: );
12515: print_debug('After calling add_quantities. return_status='||l_return_status);
12516:
12517: IF l_return_status = fnd_api.g_ret_sts_error THEN
12513: , p_check_mark_node_only => fnd_api.g_true
12514: );
12515: print_debug('After calling add_quantities. return_status='||l_return_status);
12516:
12517: IF l_return_status = fnd_api.g_ret_sts_error THEN
12518: RAISE fnd_api.g_exc_error;
12519: End IF ;
12520:
12521: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12514: );
12515: print_debug('After calling add_quantities. return_status='||l_return_status);
12516:
12517: IF l_return_status = fnd_api.g_ret_sts_error THEN
12518: RAISE fnd_api.g_exc_error;
12519: End IF ;
12520:
12521: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12522: RAISE fnd_api.g_exc_unexpected_error;
12517: IF l_return_status = fnd_api.g_ret_sts_error THEN
12518: RAISE fnd_api.g_exc_error;
12519: End IF ;
12520:
12521: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12522: RAISE fnd_api.g_exc_unexpected_error;
12523: End IF;
12524: print_debug('Before calling Query_Tree');
12525: -- query the quantities after the update
12518: RAISE fnd_api.g_exc_error;
12519: End IF ;
12520:
12521: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12522: RAISE fnd_api.g_exc_unexpected_error;
12523: End IF;
12524: print_debug('Before calling Query_Tree');
12525: -- query the quantities after the update
12526: query_tree(
12524: print_debug('Before calling Query_Tree');
12525: -- query the quantities after the update
12526: query_tree(
12527: p_api_version_number => 1.0
12528: , p_init_msg_lst => fnd_api.g_false
12529: , x_return_status => l_return_status
12530: , x_msg_count => x_msg_count
12531: , x_msg_data => x_msg_data
12532: , p_tree_id => p_tree_id
12550: , p_cost_group_id => p_cost_group_id
12551: );
12552: print_debug('After calling Query_Tree return_status='||l_return_status);
12553:
12554: IF l_return_status = fnd_api.g_ret_sts_error THEN
12555: RAISE fnd_api.g_exc_error;
12556: End IF ;
12557:
12558: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12551: );
12552: print_debug('After calling Query_Tree return_status='||l_return_status);
12553:
12554: IF l_return_status = fnd_api.g_ret_sts_error THEN
12555: RAISE fnd_api.g_exc_error;
12556: End IF ;
12557:
12558: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12559: RAISE fnd_api.g_exc_unexpected_error;
12554: IF l_return_status = fnd_api.g_ret_sts_error THEN
12555: RAISE fnd_api.g_exc_error;
12556: End IF ;
12557:
12558: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12559: RAISE fnd_api.g_exc_unexpected_error;
12560: End IF;
12561:
12562: x_return_status := l_return_status;
12555: RAISE fnd_api.g_exc_error;
12556: End IF ;
12557:
12558: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
12559: RAISE fnd_api.g_exc_unexpected_error;
12560: End IF;
12561:
12562: x_return_status := l_return_status;
12563:
12567: END IF;
12568:
12569: EXCEPTION
12570:
12571: WHEN fnd_api.g_exc_error THEN
12572: x_return_status := fnd_api.g_ret_sts_error;
12573:
12574: -- Get message count and data
12575: fnd_msg_pub.count_and_get
12568:
12569: EXCEPTION
12570:
12571: WHEN fnd_api.g_exc_error THEN
12572: x_return_status := fnd_api.g_ret_sts_error;
12573:
12574: -- Get message count and data
12575: fnd_msg_pub.count_and_get
12576: ( p_count => x_msg_count
12576: ( p_count => x_msg_count
12577: , p_data => x_msg_data
12578: );
12579:
12580: WHEN fnd_api.g_exc_unexpected_error THEN
12581: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12582:
12583: -- Get message count and data
12584: fnd_msg_pub.count_and_get
12577: , p_data => x_msg_data
12578: );
12579:
12580: WHEN fnd_api.g_exc_unexpected_error THEN
12581: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12582:
12583: -- Get message count and data
12584: fnd_msg_pub.count_and_get
12585: ( p_count => x_msg_count
12586: , p_data => x_msg_data
12587: );
12588:
12589: WHEN OTHERS THEN
12590: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12591:
12592: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
12593: THEN
12594: fnd_msg_pub.add_exc_msg
12642: (
12643: x_return_status OUT NOCOPY VARCHAR2
12644: , p_tree_id IN INTEGER
12645: ) IS
12646: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
12647: l_root_id INTEGER;
12648: l_api_name VARCHAR2(30) := 'BACKUP_TREE';
12649: BEGIN
12650: IF g_debug = 1 THEN
12654: IF is_tree_valid(l_root_id) = FALSE THEN
12655: fnd_message.set_name('INV', 'INV-Qtyroot not found');
12656: fnd_message.set_token('ROUTINE', 'Backup_Tree');
12657: fnd_msg_pub.ADD;
12658: RAISE fnd_api.g_exc_error;
12659: END IF;
12660:
12661: save_rootinfo(l_root_id);
12662: -- bug 6683013, backing up rsv_tree
12681: END IF;
12682:
12683: EXCEPTION
12684:
12685: WHEN fnd_api.g_exc_error THEN
12686: x_return_status := fnd_api.g_ret_sts_error;
12687:
12688: WHEN fnd_api.g_exc_unexpected_error THEN
12689: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12682:
12683: EXCEPTION
12684:
12685: WHEN fnd_api.g_exc_error THEN
12686: x_return_status := fnd_api.g_ret_sts_error;
12687:
12688: WHEN fnd_api.g_exc_unexpected_error THEN
12689: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12690:
12684:
12685: WHEN fnd_api.g_exc_error THEN
12686: x_return_status := fnd_api.g_ret_sts_error;
12687:
12688: WHEN fnd_api.g_exc_unexpected_error THEN
12689: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12690:
12691:
12692: WHEN OTHERS THEN
12685: WHEN fnd_api.g_exc_error THEN
12686: x_return_status := fnd_api.g_ret_sts_error;
12687:
12688: WHEN fnd_api.g_exc_unexpected_error THEN
12689: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12690:
12691:
12692: WHEN OTHERS THEN
12693: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12689: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12690:
12691:
12692: WHEN OTHERS THEN
12693: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12694:
12695: END backup_tree;
12696:
12697: -- restore a node and its children using the backup tables
12732: (
12733: x_return_status OUT NOCOPY VARCHAR2
12734: , p_tree_id IN INTEGER
12735: ) IS
12736: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
12737: l_root_id INTEGER;
12738: l_api_name VARCHAR2(30) := 'RESTORE_TREE';
12739: BEGIN
12740: IF g_debug = 1 THEN
12745: IF is_saved_tree_valid(l_root_id) = FALSE THEN
12746: fnd_message.set_name('INV', 'INV-Qtyroot not found');
12747: fnd_message.set_token('ROUTINE', 'Restore_Tree');
12748: fnd_msg_pub.ADD;
12749: RAISE fnd_api.g_exc_error;
12750: END IF;
12751:
12752: restore_rootinfo(l_root_id);
12753: -- bug 6683013, restoring back rsv_tree
12771: END IF;
12772:
12773: EXCEPTION
12774:
12775: WHEN fnd_api.g_exc_error THEN
12776: x_return_status := fnd_api.g_ret_sts_error;
12777:
12778: WHEN fnd_api.g_exc_unexpected_error THEN
12779: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12772:
12773: EXCEPTION
12774:
12775: WHEN fnd_api.g_exc_error THEN
12776: x_return_status := fnd_api.g_ret_sts_error;
12777:
12778: WHEN fnd_api.g_exc_unexpected_error THEN
12779: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12780:
12774:
12775: WHEN fnd_api.g_exc_error THEN
12776: x_return_status := fnd_api.g_ret_sts_error;
12777:
12778: WHEN fnd_api.g_exc_unexpected_error THEN
12779: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12780:
12781:
12782: WHEN OTHERS THEN
12775: WHEN fnd_api.g_exc_error THEN
12776: x_return_status := fnd_api.g_ret_sts_error;
12777:
12778: WHEN fnd_api.g_exc_unexpected_error THEN
12779: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12780:
12781:
12782: WHEN OTHERS THEN
12783: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12779: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12780:
12781:
12782: WHEN OTHERS THEN
12783: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12784:
12785: END restore_tree;
12786:
12787:
12823: x_return_status OUT NOCOPY VARCHAR2
12824: , p_tree_id IN INTEGER
12825: , x_backup_id OUT NOCOPY NUMBER
12826: ) IS
12827: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
12828: l_root_id INTEGER;
12829: l_backup_id NUMBER;
12830: l_api_name VARCHAR2(30) := 'BACKUP_TREE';
12831: BEGIN
12837: IF is_tree_valid(l_root_id) = FALSE THEN
12838: fnd_message.set_name('INV', 'INV-Qtyroot not found');
12839: fnd_message.set_token('ROUTINE', 'Backup_Tree');
12840: fnd_msg_pub.ADD;
12841: RAISE fnd_api.g_exc_error;
12842: END IF;
12843:
12844: g_backup_tree_counter := g_backup_tree_counter + 1;
12845: l_backup_id := g_backup_tree_counter;
12863: END IF;
12864:
12865: EXCEPTION
12866:
12867: WHEN fnd_api.g_exc_error THEN
12868: x_return_status := fnd_api.g_ret_sts_error;
12869:
12870: WHEN fnd_api.g_exc_unexpected_error THEN
12871: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12864:
12865: EXCEPTION
12866:
12867: WHEN fnd_api.g_exc_error THEN
12868: x_return_status := fnd_api.g_ret_sts_error;
12869:
12870: WHEN fnd_api.g_exc_unexpected_error THEN
12871: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12872:
12866:
12867: WHEN fnd_api.g_exc_error THEN
12868: x_return_status := fnd_api.g_ret_sts_error;
12869:
12870: WHEN fnd_api.g_exc_unexpected_error THEN
12871: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12872:
12873: WHEN OTHERS THEN
12874: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12867: WHEN fnd_api.g_exc_error THEN
12868: x_return_status := fnd_api.g_ret_sts_error;
12869:
12870: WHEN fnd_api.g_exc_unexpected_error THEN
12871: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12872:
12873: WHEN OTHERS THEN
12874: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12875:
12870: WHEN fnd_api.g_exc_unexpected_error THEN
12871: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12872:
12873: WHEN OTHERS THEN
12874: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12875:
12876: END backup_tree;
12877:
12878: -- Procedure
12888: x_return_status OUT NOCOPY VARCHAR2
12889: , p_tree_id IN INTEGER
12890: , p_backup_id IN NUMBER
12891: ) IS
12892: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
12893: l_root_id INTEGER;
12894: l_loop_index NUMBER;
12895: l_node_index NUMBER;
12896: l_api_name VARCHAR2(30) := 'RESTORE_TREE';
12899: print_debug(l_api_name || ' Entered',9);
12900: END IF;
12901:
12902: IF NOT g_demand_info.exists(p_tree_id) THEN
12903: raise fnd_api.g_exc_unexpected_error;
12904: END IF;
12905: IF NOT g_backup_trees.exists(p_backup_id) THEN
12906: raise fnd_api.g_exc_unexpected_error;
12907: END IF;
12902: IF NOT g_demand_info.exists(p_tree_id) THEN
12903: raise fnd_api.g_exc_unexpected_error;
12904: END IF;
12905: IF NOT g_backup_trees.exists(p_backup_id) THEN
12906: raise fnd_api.g_exc_unexpected_error;
12907: END IF;
12908:
12909: l_root_id := g_demand_info(p_tree_id).root_id;
12910:
12908:
12909: l_root_id := g_demand_info(p_tree_id).root_id;
12910:
12911: IF l_root_id <> g_backup_trees(p_backup_id).root_id THEN
12912: raise fnd_api.g_exc_unexpected_error;
12913: END IF;
12914:
12915: l_loop_index := g_backup_trees(p_backup_id).first_node;
12916:
12929: END IF;
12930:
12931: EXCEPTION
12932:
12933: WHEN fnd_api.g_exc_error THEN
12934: x_return_status := fnd_api.g_ret_sts_error;
12935:
12936: WHEN fnd_api.g_exc_unexpected_error THEN
12937: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12930:
12931: EXCEPTION
12932:
12933: WHEN fnd_api.g_exc_error THEN
12934: x_return_status := fnd_api.g_ret_sts_error;
12935:
12936: WHEN fnd_api.g_exc_unexpected_error THEN
12937: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12938:
12932:
12933: WHEN fnd_api.g_exc_error THEN
12934: x_return_status := fnd_api.g_ret_sts_error;
12935:
12936: WHEN fnd_api.g_exc_unexpected_error THEN
12937: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12938:
12939:
12940: WHEN OTHERS THEN
12933: WHEN fnd_api.g_exc_error THEN
12934: x_return_status := fnd_api.g_ret_sts_error;
12935:
12936: WHEN fnd_api.g_exc_unexpected_error THEN
12937: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12938:
12939:
12940: WHEN OTHERS THEN
12941: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12937: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12938:
12939:
12940: WHEN OTHERS THEN
12941: x_return_status := fnd_api.g_ret_sts_unexp_error ;
12942:
12943: END restore_tree;
12944:
12945:
12948: --
12949: -- simple test
12950: PROCEDURE test
12951: IS
12952: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
12953: l_msg_count NUMBER;
12954: l_msg_data VARCHAR2(1000);
12955: l_tree_id INTEGER;
12956: l_msg VARCHAR2(1000);
12958: NULL;
12959: create_tree
12960: (
12961: p_api_version_number => 1.0
12962: , p_init_msg_lst => fnd_api.g_true
12963: , x_return_status => l_return_status
12964: , x_msg_count => l_msg_count
12965: , x_msg_data => l_msg_data
12966: , p_organization_id => 207
13031: x_return_status OUT NOCOPY VARCHAR2
13032: , p_pipename IN VARCHAR2
13033: , p_node_index IN INTEGER
13034: ) IS
13035: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
13036: l_node_level INTEGER;
13037: l_node_index INTEGER;
13038: l_buf VARCHAR2(4096);
13039: l_inventory_item_id NUMBER;
13140: fnd_message.set_name('INV', 'INV-FAILED_TO_SEND_MESSAGE');
13141: fnd_msg_pub.add;
13142: -- dbm_output.put_line('Error: failed to send message to the pipe.'
13143: -- || 'Error code is '||To_char(l_rval));
13144: RAISE fnd_api.g_exc_error;
13145: END IF ;
13146:
13147: l_node_index := g_nodes(p_node_index).first_child_index;
13148:
13152: , p_pipename => p_pipename
13153: , p_node_index => l_node_index
13154: );
13155:
13156: IF l_return_status = fnd_api.g_ret_sts_error THEN
13157: RAISE fnd_api.g_exc_error;
13158: END IF ;
13159:
13160: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
13153: , p_node_index => l_node_index
13154: );
13155:
13156: IF l_return_status = fnd_api.g_ret_sts_error THEN
13157: RAISE fnd_api.g_exc_error;
13158: END IF ;
13159:
13160: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
13161: RAISE fnd_api.g_exc_unexpected_error;
13156: IF l_return_status = fnd_api.g_ret_sts_error THEN
13157: RAISE fnd_api.g_exc_error;
13158: END IF ;
13159:
13160: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
13161: RAISE fnd_api.g_exc_unexpected_error;
13162: END IF;
13163:
13164: l_node_index := g_nodes(l_node_index).next_sibling_index;
13157: RAISE fnd_api.g_exc_error;
13158: END IF ;
13159:
13160: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
13161: RAISE fnd_api.g_exc_unexpected_error;
13162: END IF;
13163:
13164: l_node_index := g_nodes(l_node_index).next_sibling_index;
13165: END LOOP;
13175: fnd_message.set_name('INV', 'INV-FAILED_TO_SEND_MESSAGE');
13176: fnd_msg_pub.add;
13177: -- dbm_output.put_line('Error: failed to send message to the pipe.'
13178: -- || 'Error code is '||To_char(l_rval));
13179: RAISE fnd_api.g_exc_error;
13180: END IF ;
13181:
13182: END IF;
13183:
13183:
13184: x_return_status := l_return_status;
13185:
13186: EXCEPTION
13187: WHEN fnd_api.g_exc_error THEN
13188: x_return_status := fnd_api.g_ret_sts_error;
13189:
13190: WHEN fnd_api.g_exc_unexpected_error THEN
13191: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13184: x_return_status := l_return_status;
13185:
13186: EXCEPTION
13187: WHEN fnd_api.g_exc_error THEN
13188: x_return_status := fnd_api.g_ret_sts_error;
13189:
13190: WHEN fnd_api.g_exc_unexpected_error THEN
13191: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13192:
13186: EXCEPTION
13187: WHEN fnd_api.g_exc_error THEN
13188: x_return_status := fnd_api.g_ret_sts_error;
13189:
13190: WHEN fnd_api.g_exc_unexpected_error THEN
13191: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13192:
13193:
13194: WHEN OTHERS THEN
13187: WHEN fnd_api.g_exc_error THEN
13188: x_return_status := fnd_api.g_ret_sts_error;
13189:
13190: WHEN fnd_api.g_exc_unexpected_error THEN
13191: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13192:
13193:
13194: WHEN OTHERS THEN
13195: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13191: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13192:
13193:
13194: WHEN OTHERS THEN
13195: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13196:
13197: END dump_tree_node;
13198:
13199: PROCEDURE dump_rootinfo
13201: x_return_status OUT NOCOPY VARCHAR2
13202: , p_pipename IN VARCHAR2
13203: , p_tree_id IN INTEGER
13204: ) IS
13205: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
13206: l_buf VARCHAR2(4096);
13207: l_tmp VARCHAR2(1000);
13208: l_rval NUMBER := NULL;
13209: BEGIN
13289: fnd_message.set_name('INV', 'INV-FAILED_TO_SEND_MESSAGE');
13290: fnd_msg_pub.add;
13291: -- dbm_output.put_line('Error: failed to send message to the pipe.'
13292: -- || 'Error code is '||To_char(l_rval));
13293: RAISE fnd_api.g_exc_error;
13294: END IF ;
13295:
13296: dump_tree_node(
13297: x_return_status => l_return_status
13298: , p_pipename => p_pipename
13299: , p_node_index => g_rootinfos(p_tree_id).item_node_index
13300: );
13301:
13302: IF l_return_status = fnd_api.g_ret_sts_error THEN
13303: RAISE fnd_api.g_exc_error;
13304: END IF ;
13305:
13306: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
13299: , p_node_index => g_rootinfos(p_tree_id).item_node_index
13300: );
13301:
13302: IF l_return_status = fnd_api.g_ret_sts_error THEN
13303: RAISE fnd_api.g_exc_error;
13304: END IF ;
13305:
13306: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
13307: RAISE fnd_api.g_exc_unexpected_error;
13302: IF l_return_status = fnd_api.g_ret_sts_error THEN
13303: RAISE fnd_api.g_exc_error;
13304: END IF ;
13305:
13306: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
13307: RAISE fnd_api.g_exc_unexpected_error;
13308: END IF;
13309:
13310: l_buf := '';
13303: RAISE fnd_api.g_exc_error;
13304: END IF ;
13305:
13306: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
13307: RAISE fnd_api.g_exc_unexpected_error;
13308: END IF;
13309:
13310: l_buf := '';
13311: l_rval := send
13318: fnd_message.set_name('INV', 'INV-FAILED_TO_SEND_MESSAGE');
13319: fnd_msg_pub.add;
13320: -- dbm_output.put_line('Error: failed to send message to the pipe.'
13321: -- || 'Error code is '||To_char(l_rval));
13322: RAISE fnd_api.g_exc_error;
13323: END IF ;
13324:
13325:
13326: x_return_status := l_return_status;
13325:
13326: x_return_status := l_return_status;
13327:
13328: EXCEPTION
13329: WHEN fnd_api.g_exc_error THEN
13330: x_return_status := fnd_api.g_ret_sts_error;
13331:
13332: WHEN fnd_api.g_exc_unexpected_error THEN
13333: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13326: x_return_status := l_return_status;
13327:
13328: EXCEPTION
13329: WHEN fnd_api.g_exc_error THEN
13330: x_return_status := fnd_api.g_ret_sts_error;
13331:
13332: WHEN fnd_api.g_exc_unexpected_error THEN
13333: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13334:
13328: EXCEPTION
13329: WHEN fnd_api.g_exc_error THEN
13330: x_return_status := fnd_api.g_ret_sts_error;
13331:
13332: WHEN fnd_api.g_exc_unexpected_error THEN
13333: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13334:
13335:
13336: WHEN OTHERS THEN
13329: WHEN fnd_api.g_exc_error THEN
13330: x_return_status := fnd_api.g_ret_sts_error;
13331:
13332: WHEN fnd_api.g_exc_unexpected_error THEN
13333: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13334:
13335:
13336: WHEN OTHERS THEN
13337: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13333: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13334:
13335:
13336: WHEN OTHERS THEN
13337: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13338:
13339: END dump_rootinfo;
13340:
13341: PROCEDURE dump_tree_to_db_pipe
13343: x_return_status OUT NOCOPY VARCHAR2
13344: , p_pipename IN VARCHAR2
13345: , p_tree_id IN INTEGER
13346: ) IS
13347: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
13348: l_rval NUMBER;
13349: BEGIN
13350: -- dbm_output.put_line('Dumping Tree: '||To_char(p_tree_id));
13351: -- check if tree id is valid
13352: IF is_tree_valid(p_tree_id) = FALSE THEN
13353: fnd_message.set_name('INV', 'INV-Qtyroot not found');
13354: fnd_message.set_token('ROUTINE', 'Dump_Tree_To_DB_Pipe');
13355: fnd_msg_pub.ADD;
13356: RAISE fnd_api.g_exc_error;
13357: END IF;
13358:
13359: dump_rootinfo
13360: (
13362: , p_pipename => p_pipename
13363: , p_tree_id => p_tree_id
13364: );
13365:
13366: IF l_return_status = fnd_api.g_ret_sts_error THEN
13367: RAISE fnd_api.g_exc_error;
13368: END IF ;
13369:
13370: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
13363: , p_tree_id => p_tree_id
13364: );
13365:
13366: IF l_return_status = fnd_api.g_ret_sts_error THEN
13367: RAISE fnd_api.g_exc_error;
13368: END IF ;
13369:
13370: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
13371: RAISE fnd_api.g_exc_unexpected_error;
13366: IF l_return_status = fnd_api.g_ret_sts_error THEN
13367: RAISE fnd_api.g_exc_error;
13368: END IF ;
13369:
13370: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
13371: RAISE fnd_api.g_exc_unexpected_error;
13372: END IF;
13373:
13374: l_rval := send
13367: RAISE fnd_api.g_exc_error;
13368: END IF ;
13369:
13370: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
13371: RAISE fnd_api.g_exc_unexpected_error;
13372: END IF;
13373:
13374: l_rval := send
13375: (
13381: fnd_message.set_name('INV', 'INV-FAILED_TO_SEND_MESSAGE');
13382: fnd_msg_pub.add;
13383: -- dbm_output.put_line('Error: failed to send message to the pipe.'
13384: -- || 'Error code is '||To_char(l_rval));
13385: RAISE fnd_api.g_exc_error;
13386: END IF ;
13387:
13388: x_return_status := l_return_status;
13389:
13388: x_return_status := l_return_status;
13389:
13390: EXCEPTION
13391:
13392: WHEN fnd_api.g_exc_error THEN
13393: x_return_status := fnd_api.g_ret_sts_error;
13394:
13395: WHEN fnd_api.g_exc_unexpected_error THEN
13396: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13389:
13390: EXCEPTION
13391:
13392: WHEN fnd_api.g_exc_error THEN
13393: x_return_status := fnd_api.g_ret_sts_error;
13394:
13395: WHEN fnd_api.g_exc_unexpected_error THEN
13396: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13397:
13391:
13392: WHEN fnd_api.g_exc_error THEN
13393: x_return_status := fnd_api.g_ret_sts_error;
13394:
13395: WHEN fnd_api.g_exc_unexpected_error THEN
13396: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13397:
13398:
13399: WHEN OTHERS THEN
13392: WHEN fnd_api.g_exc_error THEN
13393: x_return_status := fnd_api.g_ret_sts_error;
13394:
13395: WHEN fnd_api.g_exc_unexpected_error THEN
13396: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13397:
13398:
13399: WHEN OTHERS THEN
13400: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13396: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13397:
13398:
13399: WHEN OTHERS THEN
13400: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13401:
13402: END dump_tree_to_db_pipe;
13403:
13404: -- Procedure
13411: (
13412: x_return_status OUT NOCOPY VARCHAR2
13413: , p_pipename IN VARCHAR2
13414: ) IS
13415: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
13416: l_rval NUMBER;
13417: l_tree_id INTEGER;
13418: l_buf VARCHAR2(4096);
13419: l_root_id NUMBER;
13434: fnd_message.set_name('INV', 'INV-FAILED_TO_SEND_MESSAGE');
13435: fnd_msg_pub.add;
13436: -- dbm_output.put_line('Error: failed to send message to the pipe.'
13437: -- || 'Error code is '||To_char(l_rval));
13438: RAISE fnd_api.g_exc_error;
13439: END IF ;
13440:
13441: FOR l_tree_id IN 1..g_all_roots_counter LOOP
13442: l_root_id := g_all_roots(l_tree_id).root_id;
13462: fnd_message.set_name('INV', 'INV-FAILED_TO_SEND_MESSAGE');
13463: fnd_msg_pub.add;
13464: -- dbm_output.put_line('Error: failed to send message to the pipe.'
13465: -- || 'Error code is '||To_char(l_rval));
13466: RAISE fnd_api.g_exc_error;
13467: END IF ;
13468:
13469: l_rval := send
13470: (
13476: fnd_message.set_name('INV', 'INV-FAILED_TO_SEND_MESSAGE');
13477: fnd_msg_pub.add;
13478: -- dbm_output.put_line('Error: failed to send message to the pipe.'
13479: -- || 'Error code is '||To_char(l_rval));
13480: RAISE fnd_api.g_exc_error;
13481: END IF ;
13482:
13483: x_return_status := l_return_status;
13484:
13483: x_return_status := l_return_status;
13484:
13485: EXCEPTION
13486:
13487: WHEN fnd_api.g_exc_error THEN
13488: x_return_status := fnd_api.g_ret_sts_error;
13489:
13490: WHEN fnd_api.g_exc_unexpected_error THEN
13491: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13484:
13485: EXCEPTION
13486:
13487: WHEN fnd_api.g_exc_error THEN
13488: x_return_status := fnd_api.g_ret_sts_error;
13489:
13490: WHEN fnd_api.g_exc_unexpected_error THEN
13491: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13492:
13486:
13487: WHEN fnd_api.g_exc_error THEN
13488: x_return_status := fnd_api.g_ret_sts_error;
13489:
13490: WHEN fnd_api.g_exc_unexpected_error THEN
13491: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13492:
13493:
13494: WHEN OTHERS THEN
13487: WHEN fnd_api.g_exc_error THEN
13488: x_return_status := fnd_api.g_ret_sts_error;
13489:
13490: WHEN fnd_api.g_exc_unexpected_error THEN
13491: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13492:
13493:
13494: WHEN OTHERS THEN
13495: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13491: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13492:
13493:
13494: WHEN OTHERS THEN
13495: x_return_status := fnd_api.g_ret_sts_unexp_error ;
13496:
13497: END dump_tree_to_db_pipe;
13498:
13499:
13508: begin
13509: for c1_rec in C1 loop
13510: INV_QUANTITY_TREE_PVT.release_lock(
13511: l_api_number,
13512: fnd_api.g_false,
13513: l_return_status,
13514: l_msg_count,
13515: l_msg_data,
13516: c1_rec.organization_id,
13514: l_msg_count,
13515: l_msg_data,
13516: c1_rec.organization_id,
13517: c1_rec.inventory_item_id);
13518: if l_return_status = fnd_api.g_ret_sts_error then
13519: Return FALSE;
13520: End if;
13521: end loop;
13522: return TRUE;
13526: -- Coding standard used in this program
13527: -- 1. PLSQL business object api coding standard
13528: -- 2. Oracle application developer's guide
13529: -- Note:
13530: -- 1. Data types are not initialized to fnd_api.g_miss_???
13531: -- 2. Procedures or functions not exposed to user do not have the following parameters:
13532: -- p_api_version, p_msg_count, p_msg_data. x_return_status is optional.
13533: -- 3. identation and character case uses plsql mode of emacs
13534: -------------------------------------------------------------------------------