use t202324

delete from RETURN_GOODS_ADDLESS_DETAIL WHERE RTNSRNO>='000170'  and RTNSRNO<='005683' AND LEN(RTNSRNO)=6
delete FROM RETURN_GOODS_DETAIL WHERE RTNSRNO>='000170'  and RTNSRNO<='005683' AND LEN(RTNSRNO)=6
delete from RETURN_GOODS_item_DETAIL 

Create Table TMP_RETURN_GOODS_DETAIL (RtnSrNo Varchar(15),RtnCustCode Varchar(7),RtnDate DateTime,RtnSuppCode Varchar(7) Foreign Key References SUPPLIER_MASTER(SuppCode),RtnRemark Varchar(255),CompCode Varchar(7) Foreign Key References COMPANY_DETAIL(CompCode),RtnCurDate DateTime,RtnCurTime DateTime,RtnFirmCode Varchar(7),RtnFirmSrNo Varchar(15),RtnUName Varchar(255),EntNo Varchar(20),DEntNo Varchar(20),AEntNo Varchar(20),CompSrNo Varchar(15))
Create Table TMP_RETURN_GOODS_ADDLESS_DETAIL (RtnSrNo Varchar(15),RtnBillNo Varchar(15),RtnGRAmt Money,RtnLrNo Varchar(25),RtnLRDate DateTime,RtnTranName Varchar(35),Status Varchar(7),CompCode Varchar(7) Foreign key References COMPANY_DETAIL (CompCode),RtnISrNo Numeric(6),TaxableAmt Money,GSTPerc Money, GSTAmt Money)

insert into TMP_RETURN_GOODS_DETAIL select * from RETURN_GOODS_DETAIL WHERE RTNSRNO<='005000' AND LEN(RTNSRNO)=6
insert into TMP_RETURN_GOODS_ADDLESS_DETAIL select * from RETURN_GOODS_ADDLESS_DETAIL WHERE RTNSRNO<='005000' AND LEN(RTNSRNO)=6
insert into TMP_RETURN_GOODS_DETAIL SELECT RIGHT('000000'+Convert(Varchar(255),CONVERT(NUMERIC(6),RtnSrNo)-5514),6), RtnCustCode, RtnDate, RtnSuppCode, RtnRemark, CompCode, RtnCurDate, RtnCurTime, RtnFirmCode, RtnFirmSrNo, RtnUName, EntNo, DEntNo, AEntNo, CompSrNo FROM RETURN_GOODS_DETAIL WHERE (RtnSrNo >= '005000') AND (LEN(RtnSrNo) = 6)
insert into TMP_RETURN_GOODS_ADDLESS_DETAIL SELECT RIGHT('000000' + CONVERT(Varchar(255), CONVERT(NUMERIC(6), RtnSrNo) - 5514), 6) AS Expr1, RtnBillNo, RtnGRAmt, RtnLrNo, RtnLRDate, RtnTranName, Status, CompCode, RtnISrNo, TaxableAmt, GSTPerc, GSTAmt FROM RETURN_GOODS_ADDLESS_DETAIL WHERE (RtnSrNo >= '005000') AND (LEN(RtnSrNo) = 6)

UPDATE TMP_RETURN_GOODS_dETAIL SET CompSrNo = RTNSrNo, RtnFirmSrNo = RTNSRNO

DELETE FROM RETURN_GOODS_ADDLESS_DETAIL  WHERE LEN(RTNSRNO)=6
DELETE FROM RETURN_GOODS_DETAIL  WHERE LEN(RTNSRNO)=6

INSERT INTO RETURN_GOODS_DETAIL SELECT * FROM TMP_RETURN_GOODS_DETAIL 
INSERT INTO RETURN_GOODS_ADDLESS_DETAIL SELECT * FROM TMP_RETURN_GOODS_ADDLESS_DETAIL 

INSERT INTO RETURN_GOODS_ITEM_DETAIL SELECT RtnSrNo, '' AS Expr1, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, 'P' AS Expr5, 0 AS Expr6, SUM(RtnGRAmt) AS Expr7, CompCode, '' AS Expr8, RtnISrNo AS Expr9, RtnISrNo AS Expr10, 0 AS Expr11, 0 AS Expr12, '' AS Expr13, 0 AS Expr14, 0 AS Expr15, 0 AS Expr16, 0 AS Expr17, 0 AS Expr18, 0 AS Expr19, 0 AS Expr20, 0 AS Expr21, 0 AS Expr22, 0 AS Expr23, 0 AS Expr24, 0 AS Expr25, 0 AS Expr26, 0 AS Expr27, 0 AS Expr28, 0 AS Expr29, '' AS Expr30, 0 AS Expr31, 0 AS Expr32, '' AS Expr33, 0 AS Expr34, 0 AS Expr35 FROM RETURN_GOODS_ADDLESS_DETAIL WHERE LEN(RTNSRNO)=6 GROUP BY RtnSrNo, CompCode, RtnISrNo