eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.INV_SELECT_INVENTORY_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 25


      SELECT entity_type
      FROM   wip_entities
      WHERE  wip_entity_id = V_source_id;

Line: 31


      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;

Line: 62


      fnd_msg_pub.add_exc_msg ('INV_SELECT_INVENTORY_PKG', 'get_source_info');

Line: 72


  #      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;

Line: 80


    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,lpn_id)

   	  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,trans.allocated_lpn_id
	  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);

Line: 103


      fnd_msg_pub.add_exc_msg ('INV_SELECT_INVENTORY_PKG', 'get_details');

Line: 114


  #      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

  cursor mtl_org_csr (p_mo_line_id number) is
  select *
  from   mtl_txn_request_lines
  where  line_id  = p_mo_line_id ;

Line: 130


  l_insert_allowed BOOLEAN ;

Line: 162


      gmi_reservation_util.println('Get Avail: inserting the data into the temp');

Line: 170


          select 1
          into   l_found
          from   mtl_available_inventory_temp
          where (revision = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).revision or
                           WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).revision is null )
          and   (lot_number = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lot_number or
                            WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lot_number is null)
          and   (subinventory_code = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).subinventory_code or
                           WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).subinventory_code is null )
          and   (locator_id = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id or
                           WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).locator_id is null )
          and   (lpn_id = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lpn_id or
                           WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).lpn_id is null )
          and   (serial_number = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).serial_number or
                           WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).serial_number is null )
          and   (grade_code = WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).grade_code or
                          WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(i).grade_code is null) ;

Line: 260


              l_insert_allowed := TRUE ;

Line: 263


                    l_insert_allowed := TRUE ;

Line: 266


                           l_insert_allowed := TRUE ;

Line: 269


                           l_insert_allowed := FALSE;

Line: 273


                      l_insert_allowed := TRUE ;

Line: 277


                   l_insert_allowed := FALSE ;

Line: 284


            IF (l_insert_allowed) THEN


               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
               );