IMPORTANT:
Commands with UPDATE, DELETE or DROP can ruin your data! Please use these queries with caution. We highly recommend that you backup your database before trying out these commands and to load them up first on a test server. Use at your own risk.

Associated SQL Command:
/* Module : Inventory Created Date : July 29, 2009 Create By : Rashid Farooq (Ovex Tech) Error Message : Transaction cannot be posted. Line items contain errors. Reason : The Error message appear when the Item Trx Quantity is not equal to the Item Serials in IV10002,the script will remove recently added exceed serial numbers from IV10002 using DEX_ROW_ID ordering. INPUT : Define Inventory Trx Document Number for @IVDOCNBR and Item Number for @ITEMNMBR e.g [@IVDOCNBR = 'PRD-0910-00017'] [@ITEMNMBR = 'TA6203011'] */ DECLARE @TRXQTY MONEY DECLARE @EXCEED MONEY DECLARE @IVDOCNBR VARCHAR(100) DECLARE @ITEMNMBR VARCHAR(100) DECLARE @DIFFERENCE INT ------------INPUT------------------ /* ENTER INFOR HERE */ SET @IVDOCNBR = 'PRD-0910-00017' SET @ITEMNMBR = 'TA6203011' ----------------------------------- SET @TRXQTY = ( SELECT TOP 1 TRXQTY FROM dbo.IV10001 WHERE IVDOCNBR = @IVDOCNBR AND ITEMNMBR = @ITEMNMBR ) SET @EXCEED = ( SELECT COUNT(*) FROM dbo.IV10002 WHERE IVDOCNBR = @IVDOCNBR AND ITEMNMBR = @ITEMNMBR ) SET @DIFFERENCE = CAST(@TRXQTY AS INT) - CAST(@EXCEED AS INT) SET @DIFFERENCE = CASE WHEN SIGN(@DIFFERENCE) = -1 THEN @DIFFERENCE * -1 ELSE @DIFFERENCE END IF @TRXQTY <> @EXCEED BEGIN print 'Difference ' + LTRIM(STR(@TRXQTY - @EXCEED)) DELETE dbo.IV10002 WHERE DEX_ROW_ID IN ( ( SELECT TOP ( CAST(@DIFFERENCE AS INTEGER) ) WITH TIES DEX_ROW_ID FROM ( SELECT * FROM dbo.IV10002 WHERE IVDOCNBR = @IVDOCNBR AND ITEMNMBR = @ITEMNMBR ) t ORDER BY t.DEX_ROW_ID DESC ) ) PRINT 'Total ' + LTRIM(STR(@DIFFERENCE)) + ' Trx(s) where delete!' END ELSE BEGIN PRINT 'Trx is OK, skip any error message just post it' END
Viewed: 10568 times

Submitted by: Rashid Farooq

About Rashid Farooq

View complete list

previous script

next script