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.

[Home] [Help]

APPS.INV_MATERIAL_STATUS_PUB SQL Statements

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

Line: 7


PROCEDURE update_status
  (  p_api_version_number        IN  NUMBER
   , p_init_msg_lst              IN  VARCHAR2 DEFAULT fnd_api.g_false
   , p_commit                    IN  VARCHAR2 DEFAULT fnd_api.g_false
   , x_return_status             OUT NOCOPY VARCHAR2
   , x_msg_count                 OUT NOCOPY NUMBER
   , x_msg_data                  OUT NOCOPY VARCHAR2
   , p_object_type               IN  VARCHAR2
   , p_status_rec                IN  INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type
   ) IS
l_api_version_number          CONSTANT NUMBER := 1.0;

Line: 18


l_api_name                    CONSTANT VARCHAR2(30):= 'UPDATE_STATUS';

Line: 20


l_status_rec	     	      INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;

Line: 94


			SELECT status_id INTO l_old_status_id FROM  mtl_secondary_inventories
			WHERE organization_id = l_status_rec.organization_id
			and secondary_inventory_name = l_status_rec.zone_code;

Line: 102


	/*Added the reservation check on status update for Bug 14709830*/
	l_sub_fl   :=  INV_MATERIAL_STATUS_PKG.validate_mtstatus(
            p_old_status_id        => l_old_status_id
           ,p_new_status_id        => l_status_rec.status_id
           ,p_subinventory_code    => l_status_rec.zone_code
           ,p_locator_id           => NULL
           ,p_organization_id      => l_status_rec.organization_id
           ,p_inventory_item_id    => l_status_rec.inventory_item_id) ;

Line: 113


        update mtl_secondary_inventories
        set status_id = l_status_rec.status_id
            , last_updated_by = l_status_rec.last_updated_by
	    , last_update_date = l_status_rec.last_update_date
	    , last_update_login = l_status_rec.last_update_login
        where organization_id = l_status_rec.organization_id
          and secondary_inventory_name = l_status_rec.zone_code;

Line: 129


		--Bug 14028205 Added the select statement
		BEGIN
			SELECT status_id INTO l_old_status_id FROM  mtl_item_locations
			WHERE organization_id = l_status_rec.organization_id
			and inventory_location_id = l_status_rec.locator_id;

Line: 139


	/*Added the reservation check on status update for Bug 14709830*/
	l_loc_fl   :=  INV_MATERIAL_STATUS_PKG.validate_mtstatus(
            p_old_status_id        => l_old_status_id
           ,p_new_status_id        => l_status_rec.status_id
           ,p_subinventory_code    => NULL
           ,p_locator_id           => l_status_rec.locator_id
           ,p_organization_id      => l_status_rec.organization_id
           ,p_inventory_item_id    => l_status_rec.inventory_item_id) ;

Line: 150


        update  mtl_item_locations
        set status_id = l_status_rec.status_id
            , last_updated_by = l_status_rec.last_updated_by
            , last_update_date = l_status_rec.last_update_date
            , last_update_login = l_status_rec.last_update_login
        where organization_id = l_status_rec.organization_id
          and inventory_location_id = l_status_rec.locator_id;

Line: 164


		 --Bug 14028205 Added the select statement
		BEGIN
			SELECT status_id INTO l_old_status_id FROM  mtl_lot_numbers
			WHERE organization_id = l_status_rec.organization_id
			and inventory_item_id = l_status_rec.inventory_item_id
			and lot_number = l_status_rec.lot_number;

Line: 175


	/*Added the reservation check on status update for Bug 14709830*/
	l_lot_fl   :=  INV_MATERIAL_STATUS_PKG.validate_mtstatus(
            p_old_status_id        => l_old_status_id
           ,p_new_status_id        => l_status_rec.status_id
           ,p_subinventory_code    => NULL
           ,p_locator_id           => NULL
           ,p_organization_id      => l_status_rec.organization_id
           ,p_inventory_item_id    => l_status_rec.inventory_item_id
	   ,P_lot_number           => l_status_rec.lot_number) ;

Line: 187


        update  mtl_lot_numbers
        set status_id = l_status_rec.status_id
            , last_updated_by = l_status_rec.last_updated_by
            , last_update_date = l_status_rec.last_update_date
            , last_update_login = l_status_rec.last_update_login
        where organization_id = l_status_rec.organization_id
          and inventory_item_id = l_status_rec.inventory_item_id
          and lot_number = l_status_rec.lot_number;

Line: 202


		 --Bug 14028205 Added the select statement
		BEGIN
			SELECT status_id INTO l_old_status_id FROM  mtl_serial_numbers
			WHERE inventory_item_id = l_status_rec.inventory_item_id
			and serial_number = l_status_rec.serial_number;

Line: 212


	/*Added the reservation check on status update for Bug 14709830*/
	l_ser_fl   :=  INV_MATERIAL_STATUS_PKG.validate_mtstatus(
            p_old_status_id        => l_old_status_id
           ,p_new_status_id        => l_status_rec.status_id
           ,p_subinventory_code    => NULL
           ,p_locator_id           => NULL
           ,p_organization_id      => l_status_rec.organization_id
           ,p_inventory_item_id    => l_status_rec.inventory_item_id);

Line: 223


       update  mtl_serial_numbers
       set status_id = l_status_rec.status_id
            , last_updated_by = l_status_rec.last_updated_by
            , last_update_date = l_status_rec.last_update_date
            , last_update_login = l_status_rec.last_update_login
        where current_organization_id = l_status_rec.organization_id
          and inventory_item_id = l_status_rec.inventory_item_id
          and serial_number = l_status_rec.serial_number;

Line: 233


        	update  mtl_serial_numbers
       		set status_id = l_status_rec.status_id
            		, last_updated_by = l_status_rec.last_updated_by
            		, last_update_date = l_status_rec.last_update_date
            		, last_update_login = l_status_rec.last_update_login
        	where current_organization_id = l_status_rec.organization_id
          	and inventory_item_id = l_status_rec.inventory_item_id
          	and serial_number > l_status_rec.serial_number and
                    serial_number <= l_status_rec.to_serial_number;

Line: 260


		 --Bug 14028205 Added the select statement
		BEGIN
			SELECT status_id INTO l_old_status_id FROM  mtl_onhand_quantities_detail
			WHERE inventory_item_id = l_status_rec.inventory_item_id
            and organization_id = l_status_rec.organization_id
			and subinventory_code = nvl(l_status_rec.zone_code, subinventory_code)
            and nvl(lot_number, '@@@@') = nvl(l_status_rec.lot_number, nvl(lot_number, '@@@@'))
            and nvl(locator_id, -9999) = nvl(l_status_rec.locator_id, nvl(locator_id, -9999))
			and nvl(lpn_id, -9999) = nvl(l_status_rec.lpn_id, -9999);

Line: 274


		/*Added the reservation check on status update for Bug 14709830*/
		l_onhand_fl   :=  INV_MATERIAL_STATUS_PKG.validate_mtstatus(
                         p_old_status_id        => l_old_status_id
                        ,p_new_status_id        => l_status_rec.status_id
                        ,p_subinventory_code    => l_status_rec.zone_code
                        ,p_locator_id           => l_status_rec.locator_id
                        ,p_organization_id      => l_status_rec.organization_id
                        ,p_inventory_item_id    => l_status_rec.inventory_item_id
                        ,P_lot_number           => l_status_rec.lot_number
                        ,p_lpn_id               => l_status_rec.lpn_id) ;

Line: 286


		update  mtl_onhand_quantities_detail
	        set status_id = l_status_rec.status_id
	      , last_updated_by = l_status_rec.last_updated_by
	      , last_update_date = l_status_rec.last_update_date
              , last_update_login = l_status_rec.last_update_login
              where inventory_item_id = l_status_rec.inventory_item_id
              and organization_id = l_status_rec.organization_id
	      and subinventory_code = nvl(l_status_rec.zone_code, subinventory_code)
              and nvl(lot_number, '@@@@') = nvl(l_status_rec.lot_number, nvl(lot_number, '@@@@'))
              and nvl(locator_id, -9999) = nvl(l_status_rec.locator_id, nvl(locator_id, -9999))
	      and nvl(lpn_id, -9999) = nvl(l_status_rec.lpn_id, -9999);

Line: 318


    /*Bug#15939164 Added the below code to give a warning message when there exists no onhand to be updated*/
       IF (p_object_type = 'H') THEN
          --Dbms_Output.put_line ('before error');

Line: 334


    INV_MATERIAL_STATUS_PKG.Insert_status_history(p_status_rec);

Line: 377


END update_status;