SQL - Cancel Sales Order - Stored Proc

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Asamco BV, Mitchel & ALex
-- Create date: 24/05/2018
-- Description: Cancels Sales Order
-- =============================================
CREATE PROCEDURE _as_abfCancelSalesOrder
    -- Add the parameters for the stored procedure here
    @SOID bigint
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
--DECLARE @SOID bigint = 1
 
--Check if you CAN actually cancel the order, and it's not already processed...
DECLARE @DocState int,
        @DocType int
SELECT @DocState = (SELECT DocState FROM InvNum WHERE AutoIndex = @SOID);
 
SELECT @DocType = (SELECT DocType FROM InvNum WHERE AutoIndex = @SOID);
 
/*VALIDATIONS:*/
IF @DocState NOT IN (1,3,8)
    BEGIN
        RaisError('SO cannot be cancelled due to DocState!',16,1);
        RETURN
    END
 
IF @DocType NOT IN (4)
    BEGIN
        RaisError('SO cannot be cancelled due to DocState (not an SO)!',16,1);
        RETURN
    END
 
--cancelling invnum:
UPDATE [InvNum] SET [DocState]=7  WHERE AutoIndex = @SOID
 
--cancelling StkItem
UPDATE StkItem
    SET QtyOnSo = QtyOnSo - COALESCE(t.Qty,0)
FROM StkItem
    LEFT JOIN (SELECT iStockCodeID, SUM(fQuantity-fQtyProcessed) as Qty FROM _btblInvoiceLines WHERE iInvoiceID = @SOID AND iStockCodeID > 0 GROUP BY iStockCodeID) t ON t.iStockCodeID = StkItem.StockLink
  WHERE StockLink IN (SELECT iStockCodeID FROM _btblInvoiceLines WHERE iInvoiceID = @SOID)
 
--Cancel wh stock:
UPDATE WhseStk
    SET WHQtyOnSO = WHQtyOnSO - COALESCE(t.Qty,0)
FROM WhseStk W
    LEFT JOIN (SELECT iStockCodeID, IL.iWarehouseID,  SUM(fQuantity-fQtyProcessed) as Qty FROM _btblInvoiceLines IL WHERE iInvoiceID = @SOID AND iStockCodeID > 0 GROUP BY iStockCodeID, IL.iWarehouseID) t ON t.iStockCodeID = w.WHStockLink AND t.iWarehouseID = W.WHWhseID
  WHERE W.WHStockLink IN (SELECT iStockCodeID FROM _btblInvoiceLines WHERE iInvoiceID = @SOID)
 
--Cancel jobs by setting different status:
UPDATE _btblJCMaster 
	SET iStatus = 2, ulJCJobStatus = 'Completed'
FROM _btblJCMaster JCM
	WHERE cDescription = (SELECT OrderNum from Invnum WHERE AutoIndex = @SOID)
END
GO