[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT entity_type
FROM wip_entities
WHERE wip_entity_id = V_source_id;
SELECT a.batch_no, b.line_no
FROM gme_batch_header a, gme_material_details b
WHERE a.batch_id = b.batch_id
AND b.material_detail_id = V_source_line_id;
fnd_msg_pub.add_exc_msg ('INV_SELECT_INVENTORY_PKG', 'get_source_info');
# This procedure is used to insert the data from mmtt table
# when select available inventory button is pressed.
###############################################################*/
PROCEDURE get_details (V_move_order_line_id IN NUMBER, X_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
X_return_status := FND_API.G_RET_STS_SUCCESS;
INSERT INTO mtl_available_inventory_temp
(transaction_temp_id,source_line_id,move_order_line_id,organization_id,inventory_item_id,
transaction_source_type_id,transaction_action_id,lot_number,subinventory_code,lot_created,
lot_expiration_date,grade_code,locator_id,onhand_qty,secondary_onhand_qty,reason_id,
transaction_qty,secondary_transaction_qty,transaction_uom,secondary_uom,revision,order_by)
SELECT trans.transaction_temp_id,trans.source_line_id,trans.move_order_line_id,
trans.organization_id,trans.inventory_item_id,
trans.transaction_source_type_id,trans.transaction_action_id,
lots.lot_number,trans.subinventory_code,mln.creation_date,
mln.expiration_date,lots.grade_code,trans.locator_id,
0 onhand_quantity,0 secondary_onhand_qty,lots.reason_id,
decode(lots.transaction_quantity,null,trans.transaction_quantity,lots.transaction_quantity),
decode(lots.secondary_quantity,null,trans.secondary_transaction_quantity,lots.secondary_quantity),
trans.transaction_uom,trans.secondary_uom_code,trans.revision,1
FROM mtl_material_transactions_temp trans, mtl_transaction_lots_temp lots, mtl_lot_numbers mln
WHERE trans.move_order_line_id = V_move_order_line_id
AND trans.organization_id = mln.organization_id (+)
AND trans.inventory_item_id = mln.inventory_item_id (+)
AND trans.transaction_temp_id = lots.transaction_temp_id (+)
AND decode(lots.lot_number,null,'-99999',lots.lot_number) = decode(mln.lot_number,null,'-99999',mln.lot_number);
fnd_msg_pub.add_exc_msg ('INV_SELECT_INVENTORY_PKG', 'get_details');
# and insert the same into temp table
###############################################################*/
PROCEDURE get_available_inventory (p_mo_line_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2) IS
l_reservations inv_reservation_global.mtl_reservation_tbl_type;
gmi_reservation_util.println('Get Avail: inserting the data into the temp');
INSERT INTO mtl_available_inventory_temp
( revision
, lot_number
, lot_expiration_date
, subinventory_code
, locator_id
, cost_group_id
, transaction_uom
, lpn_id
, serial_number
, onhand_qty
, secondary_onhand_qty
, grade_code
, consist_string
, order_by_string
)
Values
(
WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).revision
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lot_number
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lot_expiration_date
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).subinventory_code
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).cost_group_id
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).transaction_uom
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lpn_id
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).serial_number
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).onhand_qty
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).secondary_onhand_qty
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).grade_code
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).consist_string
,WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).order_by_string
);