Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP forum
Suggested answer

SQL query for Item Stock Inquiry

Posted on by 132

Hi,

I have a requirement at my firm, we need to see the Qty on hand change between last month and the current month, To check where we are at with our Inventory

and the same we have to do with QTY on order.

I know in UI we can check per Item using the Item stock inquiry window, and HITB report but they want this info on Power BI. 

So I am looking for tables or queries to fetch the details in Power BI.

During my search on the internet, I found a lot of queries based on HITB but it is giving me the wrong result.

I tried using table SEE30303 but couldn't get anything right out of it

If anyone could help would be great.

Categories:
  • Suggested answer
    Andrew John Dean Profile Picture
    Andrew John Dean 1,335 on at
    RE: SQL query for Item Stock Inquiry

    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

  • Suggested answer
    Andrew John Dean Profile Picture
    Andrew John Dean 1,335 on at
    RE: SQL query for Item Stock Inquiry

    Hi

    I have a script that looks through various historical, non-HITB tables and returns the stock level of an item at a specified date. Please send me a private forum message with your email and I will forward the script to you.

    Regards
    Andrew Dean
    Envisage.jpg

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,914 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,549 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans