Brian,
Add unique post fix to new Invoice number to resolve round trip between OLD n new numbers.
Update below list of tables with new invoice number (Added unique post fix) based on your reference table.
Again update all tables with SOPNUMBE = SOPNUMBE - Remove Post fix .
Below SQL script may be helps you. I have update the each record one by one. You can use single update statement for each table instead.
Try to execute below SQL routine on TEST environment.
Let me know if I missed to add any subset tables.
USE DBNAME
DECLARE @OLD_SOPNUMBE CHAR(21), @NEW_SOPNUMBE CHAR(21)--,@CUSTNMBR CHAR(15)
DECLARE GETSOPNUMBE CURSOR FOR --GET OLD & NEW SOPNUMBER
SELECT DISTINCT LTRIM(RTRIM(OLD)) AS OLD ,LTRIM(RTRIM(NEW)) AS NEW FROM REF_TABLE
OPEN GETSOPNUMBE
FETCH NEXT FROM GETSOPNUMBE INTO @OLD_SOPNUMBE ,@NEW_SOPNUMBE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @NEW_SOPNUMBE = LTRIM(RTRIM(@NEW_SOPNUMBE)) + '-9' /*Add Unique Post Fix '-9' */
UPDATE SOP30200 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE /* Historical Transactions (header) */
UPDATE SOP30300 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE /*Historical Transactions (line detail) */
UPDATE SOP10102 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE /* GL Distributions Work and History */
UPDATE SOP60100 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE /* SOP-POP Link */
UPDATE RM20201 SET APTODCNM = @NEW_SOPNUMBE WHERE APTODCTY = 1 AND APTODCNM = @OLD_SOPNUMBE /* Open Transactions Apply */
UPDATE RM30201 SET APTODCNM = @NEW_SOPNUMBE WHERE APTODCTY = 1 AND APTODCNM = @OLD_SOPNUMBE /* Historical Transactions Apply */
UPDATE RM30101 SET DOCNUMBR = @NEW_SOPNUMBE,TRXDSCRN=@NEW_SOPNUMBE WHERE RMDTYPAL = 1 AND DOCNUMBR = @OLD_SOPNUMBE /*Historical Transactions */
UPDATE SOP10106 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE /* User Defined Work and History */
UPDATE SOP10202 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE /* Line Comment Work and History */
UPDATE SOP10105 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE /* Sales Taxes Work and History */
UPDATE RM10601 SET DOCNUMBR = @NEW_SOPNUMBE WHERE RMDTYPAL = 1 AND DOCNUMBR = @OLD_SOPNUMBE /* RM Tax Work File */
UPDATE RM30301 SET DOCNUMBR = @NEW_SOPNUMBE WHERE RMDTYPAL = 1 AND DOCNUMBR = @OLD_SOPNUMBE /* Distribution History */
UPDATE RM30601 SET DOCNUMBR = @NEW_SOPNUMBE WHERE RMDTYPAL = 1 AND DOCNUMBR = @OLD_SOPNUMBE /* RM Tax History File */
UPDATE RM00401 SET DOCNUMBR = @NEW_SOPNUMBE WHERE RMDTYPAL = 1 AND DOCNUMBR = @OLD_SOPNUMBE /* RM Key File */
UPDATE RM20101 SET DOCNUMBR = @NEW_SOPNUMBE,TRXDSCRN=@NEW_SOPNUMBE WHERE RMDTYPAL = 1 AND DOCNUMBR = @OLD_SOPNUMBE /* Open Transactions */
FETCH NEXT FROM GETSOPNUMBE INTO @OLD_SOPNUMBE ,@NEW_SOPNUMBE
END
CLOSE GETSOPNUMBE
DEALLOCATE GETSOPNUMBE
/*Update Invoice number (Remove post fix) */
DECLARE GETSOPNUMBE1 CURSOR FOR --GET ALL NEW SOPNUMBER which have Post fix '-9'
SELECT LTRIM(RTRIM(SOPNUMBE)) AS SOPNUMBE ,REPLACE(LTRIM(RTRIM(SOPNUMBE)),'-9','') AS SOPNUMBE_NEW /*Remove Post fix*/ FROM SOP30200 where LTRIM(RTRIM(SOPNUMBE)) like '%-9%'
OPEN GETSOPNUMBE1
FETCH NEXT FROM GETSOPNUMBE1 INTO @OLD_SOPNUMBE ,@NEW_SOPNUMBE
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE SOP30200 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE
UPDATE SOP30300 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE
UPDATE SOP10102 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE
UPDATE SOP60100 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE
UPDATE RM20201 SET APTODCNM = @NEW_SOPNUMBE WHERE APTODCTY = 1 AND APTODCNM = @OLD_SOPNUMBE
UPDATE RM30201 SET APTODCNM = @NEW_SOPNUMBE WHERE APTODCTY = 1 AND APTODCNM = @OLD_SOPNUMBE
UPDATE RM30101 SET DOCNUMBR = @NEW_SOPNUMBE,TRXDSCRN=@NEW_SOPNUMBE WHERE RMDTYPAL = 1 AND DOCNUMBR = @OLD_SOPNUMBE
UPDATE SOP10106 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE
UPDATE SOP10202 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE
UPDATE SOP10105 SET SOPNUMBE = @NEW_SOPNUMBE WHERE SOPTYPE = 3 AND SOPNUMBE = @OLD_SOPNUMBE
UPDATE RM10601 SET DOCNUMBR = @NEW_SOPNUMBE WHERE RMDTYPAL = 1 AND DOCNUMBR = @OLD_SOPNUMBE
UPDATE RM30301 SET DOCNUMBR = @NEW_SOPNUMBE WHERE RMDTYPAL = 1 AND DOCNUMBR = @OLD_SOPNUMBE
UPDATE RM30601 SET DOCNUMBR = @NEW_SOPNUMBE WHERE RMDTYPAL = 1 AND DOCNUMBR = @OLD_SOPNUMBE
UPDATE RM00401 SET DOCNUMBR = @NEW_SOPNUMBE WHERE RMDTYPAL = 1 AND DOCNUMBR = @OLD_SOPNUMBE
UPDATE RM20101 SET DOCNUMBR = @NEW_SOPNUMBE,TRXDSCRN=@NEW_SOPNUMBE WHERE RMDTYPAL = 1 AND DOCNUMBR = @OLD_SOPNUMBE
END
FETCH NEXT FROM GETSOPNUMBE1 INTO @OLD_SOPNUMBE ,@NEW_SOPNUMBE
END
CLOSE GETSOPNUMBE1
DEALLOCATE GETSOPNUMBE1
Regards,
Vinod Agrawal
www.flovianit.com
