This is the first blog post of the 5 part series. In this series of blog post I am going to write some SQL queries which Dynamics AX consultants or administrators can use them in everyday life. The idea is to leverage the existing skillset. In this post I am going to show you how you can extract the inventory on-hand through SQL. In order to run this query you require at-least read-only access to the Dynamics AX database.

 

SET NOCOUNT ON

/*

      This is the rea-only query. It fetches the current on-hand. You can use it to find on-hand for:

            1. All items within a given AX company.

            2. All items belong to a specific item group withing the a given AX company.

            3. For specific item within a given company.

*/

DECLARE     @DATAAREAID       VARCHAR(100),

            @ITEMID                 VARCHAR(100),

            @ITEMGROUPID      VARCHAR(100),

            @LANGID                 VARCHAR(100)

 

SELECT      @DATAAREAID = ‘CEU’ –Required

SELECT      @ITEMID = –Can be leave as blank

SELECT      @ITEMGROUPID = –Can be leave as blank

SELECT      @LANGID = T2.LANGUAGEID

FROM  DATAAREA T1

INNER JOIN DIRPARTYTABLE T2 ON T1.ID = T2.DATAAREA

WHERE T1.ID = @DATAAREAID

IF @LANGID IS NULL

BEGIN

SELECT @LANGID = SYSTEMLANGUAGEID FROM SYSTEMPARAMETERS

END

SELECT      T3.ITEMID,

            T7.NAME,

            T5.ITEMGROUPID,

            T6.NAME,

            T1.POSTEDQTY,

            T4.UNITID,

            T2.INVENTSITEID,

            T2.INVENTLOCATIONID,

            T2.WMSLOCATIONID,

            T2.INVENTBATCHID,

            T2.INVENTSERIALID,

            T2.WMSPALLETID

FROM INVENTSUM T1

INNER JOIN INVENTDIM T2 ON T1.INVENTDIMID = T2.INVENTDIMID

INNER JOIN INVENTTABLE T3 ON T3.ITEMID = T1.ITEMID

INNER JOIN INVENTTABLEMODULE T4 ON T1.ITEMID = T4.ITEMID AND T4.MODULETYPE = 0

INNER JOIN INVENTITEMGROUPITEM T5 ON T5.ITEMID = T1.ITEMID

INNER JOIN INVENTITEMGROUP T6 ON T6.ITEMGROUPID = T5.ITEMGROUPID

INNER JOIN ECORESPRODUCTTRANSLATION T7 ON T3.PRODUCT = T7.PRODUCT

AND T7.LANGUAGEID = @LANGID

WHERE T1.DATAAREAID = @DATAAREAID

AND T2.DATAAREAID = @DATAAREAID

AND T3.DATAAREAID = @DATAAREAID

AND T4.DATAAREAID = @DATAAREAID

AND T5.ITEMDATAAREAID = @DATAAREAID

AND T6.DATAAREAID = @DATAAREAID

AND T1.ITEMID = CASE WHEN ((@ITEMID IS NOT NULL) AND (@ITEMID <> ”)) THEN @ITEMID ELSE T1.ITEMID END

AND T6.ITEMGROUPID = CASE WHEN ((@ITEMGROUPID IS NOT NULL) AND (@ITEMGROUPID <> ”)) THEN @ITEMGROUPID ELSE T6.ITEMGROUPID END

ORDER BY T1.ITEMID

Leave a Reply

Recent Comments

    Archives

    Categories