USE T202324


alter table RETURN_GOODS_DETAIL add ISrNo Numeric(6)
-------------------------------------------------------------
UPDATE RETURN_GOODS_DETAIL Set ISrNo=1 WHERE RtnSrNo='000001' 

DECLARE @RFirmCode Varchar(6), @RRtnSrNo Varchar(50), @TmpISrNo Numeric(6), @TempFirmCode Varchar(6) Set @TmpISrNo=0
DECLARE CUR1 CURSOR FOR SELECT RtnFirmCode,RtnSrNo FROM RETURN_GOODS_DETAIL WHERE LEN(RtnSrNo)=6 ORDER BY RtnFirmCode,RtnSrNo 
OPEN CUR1 FETCH NEXT FROM CUR1 INTO @RFirmCode, @RRtnSrNo
Set @TmpISrNo=1 SET @TempFirmCode = (SELECT RtnFirmCode FROM RETURN_GOODS_DETAIL WHERE RtnSrNo='000001')
WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT FROM CUR1 INTO @RFirmCode, @RRtnSrNo
IF @TempFirmCode<>@RFirmCode 
	BEGIN SET @TmpISrNo=1 SET @TempFirmCode=@RFirmCode END 
ELSE 
	BEGIN Set @TmpISrNo=@TmpISrNo+1 END 
UPDATE RETURN_GOODS_DETAIL Set ISrNo=@TmpISrNo WHERE RtnSrNo=@RRtnSrNo END CLOSE CUR1 DEALLOCATE CUR1
-------------------------------------------------------------
UPDATE RETURN_GOODS_DETAIL  SET RtnFirmSrNo = RIGHT('000000'+CONVERT(VARCHAR(255),ISrNo),6) WHERE LEN(RTNSRNO)=6
UPDATE RETURN_GOODS_DETAIL  SET cOMPSrNo = RIGHT('000000'+CONVERT(VARCHAR(255),ISrNo),6) WHERE LEN(RTNSRNO)=6
-------------------------------------------------------------
alter table RETURN_GOODS_DETAIL DROP COLUMN ISrNo
-------------------------------------------------------------

--SELECT RtnFirmCode,RtnSrNo, ISRNO FROM RETURN_GOODS_DETAIL WHERE LEN(RtnSrNo)=6 ORDER BY RtnFirmCode,RtnSrNo