CREATE Procedure [dbo].[LabTestDetailsMigrationScript](@Group_id int,  @User_Login  Varchar(50)) AS
Declare
	@InsertStat			varchar(4000),
	@Loop7				Int,
	@Test_id			numeric(20,0),
	@TEST_ABBREVIATION	Varchar(50),
	@SPECIMEN_TYPE_ID	numeric(20,0),
	@CONTAINER_ID		numeric(20,0),
	@TP_Item_Deal_id	varchar(50)
BEGIN
	DECLARE update_Test_details_Cursor Cursor for
	SELECT TEST_ID,TEST_ABBREVIATION,TEST_SPECIMEN,TEST_CONTAINER,TP_ITEM_DEAL_ID FROM EMRLabTestLkup ELTL,EMRServiceItems SI WHERE ELTL.EMR_Service_ID=SI.EMR_SERVICE_ID AND ELTL.GROUP_ID IS NOT NULL AND ELTL.LOCATION_ID IS NOT NULL AND ELTL.LOCATION_ID!=0
	OPEN update_Test_details_Cursor 
	FETCH NEXT FROM update_Test_details_Cursor INTO @Test_id,@TEST_ABBREVIATION,@SPECIMEN_TYPE_ID,@CONTAINER_ID,@TP_Item_Deal_id
	SET @Loop7 = @@FETCH_Status 
	WHILE @Loop7 = 0
	BEGIN
		SET @InsertStat = 'Update EMRLabTestLkup Set '
		if(isnull(@TEST_ABBREVIATION,'')='')
			SET @InsertStat = @InsertStat + 'TEST_ABBREVIATION=NULL'
		else 
			SET @InsertStat = @InsertStat + 'TEST_ABBREVIATION='+char(39)+Rtrim(@TEST_ABBREVIATION)+char(39)
		if(isnull(@SPECIMEN_TYPE_ID,-99999999)=-99999999)
			SET @InsertStat = @InsertStat + ',TEST_SPECIMEN=NULL'
		else if(len(Rtrim(cast(@SPECIMEN_TYPE_ID as varchar)))>4)
			SET @InsertStat = @InsertStat +',TEST_SPECIMEN='+char(39)+substring(Rtrim(cast(@SPECIMEN_TYPE_ID as varchar)),3,len(Rtrim(cast(@SPECIMEN_TYPE_ID as varchar))))+char(39)
		else
			SET @InsertStat = @InsertStat +',TEST_SPECIMEN='+char(39)+Rtrim(cast(@SPECIMEN_TYPE_ID as varchar))+char(39)
		if(isnull(@CONTAINER_ID,-99999999)=-99999999)
			SET @InsertStat = @InsertStat + ',TEST_CONTAINER=NULL'
		else if(len(Rtrim(cast(@CONTAINER_ID as varchar)))>4)
			SET @InsertStat = @InsertStat + ',TEST_CONTAINER='+substring(Rtrim(Cast(@CONTAINER_ID as varchar)),3,len(Rtrim(cast(@CONTAINER_ID as varchar))))
		else
			SET @InsertStat = @InsertStat + ',TEST_CONTAINER='+Rtrim(Cast(@CONTAINER_ID as varchar))
		SET @InsertStat = @InsertStat + ' WHERE TEST_ID IN (Select el.TEST_ID From EMRLabTestLkup el inner join EMRServiceItems es on el.emr_service_id=es.emr_service_id Where Rtrim(es.TP_ITEM_DEAL_ID) = ' + Char(39) + Rtrim(@TP_Item_Deal_id) + Char(39) +')'
		print @InsertStat
		FETCH NEXT FROM update_Test_details_Cursor INTO @Test_id,@TEST_ABBREVIATION,@SPECIMEN_TYPE_ID,@CONTAINER_ID,@TP_Item_Deal_id
	END
	CLOSE update_Test_details_Cursor
	DEALLOCATE update_Test_details_Cursor
END