Quantity on hand of an item at each location

Finance and Operations/AX Developer Forum
1

Hello,

In Dynamics AX Application tables, InventItemLocation has wmsPickingLocation for each ItemId, and InventCountJour table has InventOnHand for each ItemId, but how do I find out which location has what quantity of the item?

Thanks.

Deepa

2

Hi Deepa,

This code help you out to get the onHand :

static void findOnHand(Args _args)
{
InventDim inventDim;
InventDimParm inventDimParm;
Itemid itemid;
InventOnHand inventOnHand = new InventOnHand();
;

// take a sample item for testing
itemid = “Item1”;

// take a combination of dimension , against which you want to find the stock
inventDim.InventLocationId = “HYD”;

//Set the flag for the selected dimensions as active.
inventDimParm.initFromInventDim(inventDim);

//initialize the inventonhand with item,dimension and dim paramter

inventOnHand.parmItemId(itemid);
inventOnHand.parmInventDim(inventDim);
inventOnHand.parmInventDimParm(inventDimParm);

// Retrieve the onhand info
info(strfmt(“Available Physical: %1”,
inventOnhand.availPhysical()));
info(strfmt(“On order: %1”,inventOnhand.onOrder()));

}

3

Thank you for your solution, but I need to do this in sql so I need to find out which table or tables contain the InventOnHand for each location of an item. I would appreciate help with this.

Deepa

4

Basically onHand qty(Information) will be available in InventSum table. Just go through that table and write your query. If you want to filter based on location then there would be field called “InventDim” which tells you about different dimensions.

5

That helps - thank you, Vishal.

6

Hi I want to filter Invent On hand based on Invent Location in AX 2012l.

7

Hi I want to filter Invent On hand based on Invent Location in AX 2012.

8

So what is stopping you to do that? Please elaborate.

9

hi kranti,

I am not able to filter data based on each loaction on InventTable,InventSum and InventDim tables combination.

Regards

Arun

10

So do you want the inventSum data group by itemId and location?

11

Yes I want to filter the data in InventSum based on wmsLocationId .

12

You can use \Data Dictionary\Tables\InventSum\Methods\newQuery

to filter and group inventsum by specific dimension

13

Hi Kranti

I have solve the problem and also published it on my blog below is the link

https://arungarg1987.wordpress.com/2015/09/23/filter-invent-on-hand-based-on-wmslocation/

Regards