SQL stored procedure to get Item's on hand on specified date

One of my client needed to have an inventory data text file generated directly from SQL query, In which there was a need have on hand inventory of items on specified date but not on inventory dimensions.
So I went through system's class InventOnhand which is responsible for getting item's on hand on provided date and replicate the same logic to get item's on hand without considering inventory dimensions but a few tweaking will be required to get on hand on different dimensions.

Here is the SP which require item number and date to get its on hand.


USE [MicrosoftDynamicsAX]
GO
/****** Object:  StoredProcedure [dbo].[FG_ItemOnHand]    Script Date: 2/5/2018 10:56:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FG_ItemOnHand] @ItemId nvarchar(20), @AsOfDate DATE, @OnHand DECIMAL(16, 2) OUTPUT
AS
DECLARE @Posted DECIMAL(16, 2),
@Received DECIMAL(16, 2),
@Deducted DECIMAL(16, 2),
@Picked DECIMAL(16, 2),
@Registered DECIMAL(16, 2);
SELECT
@Posted = sum(PostedQty),
@Received = sum(Received),
@Deducted = sum(Deducted),
@Picked = sum(PICKED),
@Registered = sum(Registered) FROM inventSum
WHERE inventSum.ItemId    = @ItemId
AND inventSum.ClosedQty   = 0;

DECLARE @Qty DECIMAL(16, 2),
@StatusIssue INT,
@StatusReceipt INT,
@PackingSlipReturned INT,
@DateFinancial DATETIME,
@DatePhysical DATETIME,
@DateInvent DATETIME
DECLARE InventTransCursor CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT sum(Qty), StatusIssue, StatusReceipt, PackingSlipReturned, DateFinancial, DatePhysical, DateInvent
FROM inventTrans
WHERE inventTrans.ItemId = @ItemId
AND DateStatus > cast(@AsOfDate AS DATETIME)
AND ((StatusReceipt <= 2 AND StatusIssue = 0)
OR (inventTrans.StatusReceipt = 0
AND inventTrans.StatusIssue <= 3))
GROUP BY StatusIssue, StatusReceipt, PackingSlipReturned, DateFinancial, DatePhysical, DateInvent, ITEMID;
OPEN InventTransCursor;
FETCH NEXT FROM InventTransCursor INTO @Qty, @StatusIssue, @StatusReceipt, @PackingSlipReturned, @DateFinancial, @DatePhysical, @DateInvent;
WHILE @@FETCH_STATUS = 0
   BEGIN
IF (@DateFinancial) > cast(@AsOfDate AS DATETIME)
BEGIN
SET @Posted -= @Qty;
BEGIN
IF (@StatusReceipt) = 1
SET @Received += @Qty;
ELSE
IF (@StatusIssue) = 1
SET @Deducted -= @Qty;
END;
IF (@PackingSlipReturned) = 1
  SET @Posted += @Qty;
END;
IF (@DatePhysical) > cast(@AsOfDate AS DATETIME)
BEGIN
IF (@StatusReceipt) = 1
SET @Received -= @Qty;
ELSE IF (@StatusReceipt) = 2
SET @Received -= @Qty;
ELSE IF (@StatusIssue) = 1
SET @Deducted += @Qty;
ELSE IF (@StatusIssue) = 2
SET @Deducted += @Qty;

IF @DateInvent <> null AND @DateInvent <= cast(@AsOfDate AS DATETIME)
BEGIN
IF @Qty > 0
SET @Registered += @Qty
ELSE
SET @Picked -= @Qty
END;
END;
      FETCH NEXT FROM InventTransCursor INTO @Qty, @StatusIssue, @StatusReceipt, @PackingSlipReturned, @DateFinancial, @DatePhysical, @DateInvent;
   END;
CLOSE InventTransCursor;
DEALLOCATE InventTransCursor;
SET @OnHand = @Posted + @Received - @Deducted - @Picked + @Registered;
RETURN


I hope you like the effort I made. :)