Dynamics Community Forum Thread Details

The following script shows the stock level of an item at a location at any particular date using non-HITB history tables.

You should be able to tweak the query to get the quantity variances between months.

DECLARE
@LOCATION varchar(11) = 'WAREHOUSE',
@ITEMNMBR varchar(31) = '128 SDRAM',
   @Module VARCHAR(10),
   @Type INT,
   @DocNo VARCHAR(50),
   @DocDate DATETIME,
   @TrxQty FLOAT,
   @OnHand FLOAT,
   @Allocated FLOAT,
   @From VARCHAR(50),
   @To VARCHAR(50),
   @Customer VARCHAR(50),
   @CustomerName VARCHAR(100),
   @Posted INT,
   @DEX_ROW_ID INT

SELECT @Allocated = ATYALLOC, @OnHand = QTYONHND FROM IV00102 WHERE ITEMNMBR = @ITEMNMBR AND LOCNCODE = @LOCATION

CREATE TABLE #Temp
(
   [Doc Date] DATETIME,
   [Module] VARCHAR(10),
   [Type] INT,
   [Doc No] VARCHAR(50),
   [Trx Qty] FLOAT,
   [On Hand] FLOAT,
   [Allocated] FLOAT,
   [From] VARCHAR(50),
   [To] VARCHAR(50),
   [Customer] VARCHAR(50),
   [Customer Name] VARCHAR(100),
   [Posted] INT,
   [DEX_ROW_ID] INT
)

DECLARE History_Cursor CURSOR FOR
   -- Unposted invoices
   SELECT
       SOP10100.DOCDATE AS [DocDate],
       'SOP' AS Module,
       SOP10100.SOPTYPE AS [Type],
       SOP10100.SOPNUMBE AS [DocNo],
       SOP10200.QUANTITY AS [TrxQty],
       SOP10200.LOCNCODE AS [From],
       '' AS [To],
       SOP10100.CUSTNMBR AS [Customer],
       RM00101.CustName as [CustomerName],
       0 as Posted,
       SOP10100.DEX_ROW_ID
   FROM SOP10200 AS SOP10200
   INNER JOIN SOP10100 AS SOP10100 ON SOP10200.SOPNUMBE = SOP10100.SOPNUMBE
   INNER JOIN RM00101 AS RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR
   WHERE SOP10200.LOCNCODE = @LOCATION
       AND SOP10200.ITEMNMBR = @ITEMNMBR

   UNION

   -- Unposted transfers and assemblies
   SELECT
       IV10000.DOCDATE AS [Doc Date],
       'IV' AS Module,
       IV10001.IVDOCTYP AS Type,
       IV10001.IVDOCNBR AS [Doc No.],
       IV10001.TRXQTY AS [TrxQty],
       IV10001.TRXLOCTN AS [From],
       IV10001.TRNSTLOC AS [To],
       '' AS [Customer],
       '' AS [Customer Name],
       0 AS Posted,
       IV10001.dex_row_id
   FROM IV10001 AS IV10001
   INNER JOIN IV10000 AS IV10000 ON IV10001.IVDOCNBR = IV10000.IVDOCNBR
   WHERE IV10001.TRXLOCTN = @LOCATION AND IV10001.ITEMNMBR = @ITEMNMBR

   UNION

   -- Posted Invoices and non transfer
   SELECT
       IV30300.DOCDATE AS [DocDate],
       IV30300.HSTMODUL AS Module,
       IV30300.DOCTYPE AS [Type],
       IV30300.DOCNUMBR AS [DocNo],
       IV30300.TRXQTY AS [TrxQty],
       IV30300.TRXLOCTN AS [From],
       '' AS [To],
       IV30300.CUSTNMBR AS [Customer],
       CASE
               WHEN RM00101.CUSTNMBR IS NOT NULL THEN RM00101.CUSTNAME
               WHEN PM00200.VENDORID IS NOT NULL THEN PM00200.VENDNAME
               ELSE '' END
       AS [CustomerName],
       1 AS Posted,
       IV30300.DEX_ROW_ID
   FROM IV30300 AS IV30300
   LEFT JOIN RM00101 AS RM00101 ON IV30300.CUSTNMBR = RM00101.CUSTNMBR
   LEFT JOIN PM00200 AS PM00200 ON IV30300.CUSTNMBR = PM00200.VENDORID
   WHERE
       IV30300.DOCTYPE <> 3
       AND IV30300.TRXLOCTN = @LOCATION
       AND IV30300.ITEMNMBR = @ITEMNMBR

   UNION

   -- Posted Transfers
   SELECT
       IV30300.DOCDATE AS [DocDate],
       IV30300.HSTMODUL AS Module,
       IV30300.DOCTYPE AS [Type],
       IV30300.DOCNUMBR AS [DocNo],
       -IV30300.TRXQTY AS [TrxQty],
       '' AS [From],
       IV30300.TRNSTLOC AS [To],
       IV30300.CUSTNMBR AS [Customer],
       '' AS [CustomerName],
       1 AS Posted,
       IV30300.DEX_ROW_ID
   FROM
       IV30300 AS IV30300
   WHERE
       IV30300.DOCTYPE = 3
       AND IV30300.TRXLOCTN = @LOCATION
       AND IV30300.ITEMNMBR = @ITEMNMBR

   UNION

   -- Posted transfers
   SELECT
       IV30300.DOCDATE AS [DocDate],
       IV30300.HSTMODUL AS Module,
       IV30300.DOCTYPE AS [Type],
       IV30300.DOCNUMBR AS [DocNo],
       IV30300.TRXQTY AS [TrxQty],
       IV30300.TRXLOCTN AS [From],
       '' AS [To],
       IV30300.CUSTNMBR AS [Customer],
       '' AS [CustomerName],
       1 AS Posted,
       IV30300.DEX_ROW_ID
   FROM
       IV30300 AS IV30300
   WHERE
       IV30300.DOCTYPE = 3
       AND IV30300.ITEMNMBR = @ITEMNMBR
       AND IV30300.TRNSTLOC = @LOCATION

   ORDER BY Posted, DOCDATE DESC, [Quantity]

OPEN History_Cursor

FETCH NEXT FROM History_Cursor INTO @DocDate, @Module, @Type, @DocNo, @TrxQty, @From, @To, @Customer, @CustomerName, @Posted, @DEX_ROW_ID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Temp SELECT @DocDate, @Module, @Type, @DocNo, @TrxQty, @OnHand, @Allocated, @From, @To, @Customer, @CustomerName, @Posted, @DEX_ROW_ID

IF @Posted = 1
SET @OnHand = @OnHand - @TrxQty

   IF @Posted = 0
   BEGIN
       IF CASE WHEN (@Module = 'IV' AND @Type IN (1,2,3)) THEN 0 ELSE 1 END = 1
       AND CASE WHEN (@Module = 'SOP' AND @Type IN (4, 5)) THEN 0 ELSE 1 END = 1
           SET @Allocated = @Allocated - @TrxQty

END
FETCH NEXT FROM History_Cursor INTO @DocDate, @Module, @Type, @DocNo, @TrxQty, @From, @To, @Customer, @CustomerName, @Posted, @DEX_ROW_ID
END

CLOSE History_Cursor
DEALLOCATE History_Cursor

SELECT * FROM #Temp
DROP TABLE #Temp

Community site session details

SQL query for Item Stock Inquiry

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

Vahid Ghafarpour – Community Spotlight

Tip: Become a User Group leader!

Leaderboard

Featured topics

Product updates