DELIMITER $$

DROP PROCEDURE IF EXISTS ft_insertion_validatio$$
CREATE PROCEDURE ft_insertion_validation
       (
	IN Splitlot INT, -- SplitlotId of the splitlot to be validated
	IN TrackingLotID VARCHAR(1024), -- Tracking lot of the splitlot to be validated
	IN LotID VARCHAR(1024), -- Lot of the splitlot to be validated
	IN WaferID VARCHAR(1024), -- WaferID of the splitlot to be inserted (not used for FT)
	OUT TrackingLotID_Out VARCHAR(1024), -- Tracking lot to be used in GexDB for this splitlot
	OUT LotID_Out VARCHAR(1024), -- Lot to be used in GexDB for this splitlot
	OUT WaferID_Out VARCHAR(1024), -- Wafer to be used in GexDB for this splitlot
	OUT ProductName VARCHAR(1024), -- Return the Product Name if it has to be overloaded
	OUT Message VARCHAR(1024), -- Return the Error message in case the validation fails
	OUT Status INT
       )
       
BEGIN

	DECLARE Temp_Part VARCHAR (255);

	SELECT part_typ INTO Temp_Part FROM ft_splitlot WHERE splitlot_id = Splitlot;
	-- Pull product number from product+step
	SELECT substring(Temp_Part,1,4) INTO Temp_Part;
	-- Update part type
	UPDATE ft_splitlot SET part_typ = Temp_Part WHERE splitlot_id = Splitlot;
	-- Pull step from product+step
  
       	SELECT TrackingLotID INTO TrackingLotID_Out From Dual;
	SELECT LotID INTO LotID_Out From Dual;
	SELECT WaferID INTO WaferID_Out From Dual;
	SELECT Temp_Part INTO ProductName From Dual;
	SELECT 'Success' INTO Message From Dual;
	SELECT 1 INTO Status From Dual;

END;

DELIMITER ;