Using SQL to Pull all warehouse locations and show details of what is in the location or if it is empty

(0) Share

Report

Posted on by Community Member

Hi Guys I need some help.

I have been having a bit of trouble trying to use SQL to Pull from Microsoft AX I need the report to show all warehouse locations and show details of what is currently in the location or if the location is empty.

Basically a full Inventory / Capacity report.

I have tried joining Inventdim and Inventsum however It seems to return many to 1 with loads of historical Item numbers for a 0 qty?

any ideas please ?

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Rustem Galiamov 8,072 on at

Like (1)

Report

Hi!

The InventSum table contains information about the present and expected on-hand stock of items. Every inventory transaction change this information.

If you want to build report about full inventory using InventSum and InventDim then you also need to keep in mind that full information of Qty it's information from a few fields (i.e. AvailPhysical, AvailOrdered...)

If you want to get information about inventory Qty on selected date - InventTrans table also needed.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi yes I thought about using the AX built in on hand report but I need to be able to make some calculations and from the data provided, create a capacity report....as to how many locations total, how many of these have stock in and how many are empty. I need it all to be in a neat report that runs 3 times a day.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I was looking some sql that shows all locations and what is currently in the location. I would need it to return If the location is full or empty and if it is full what is in it.. Then I need to produce a capacity report from that to show how many locations we have in total and of that total how many have stock in them and how many are empty.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (1)

Report
I think this is probably a good starting point based on your initial question.

To your point, it starts with WMSLOCATION and does a LEFT JOIN on the results of INVENTSUM/INVENTDIM. I used the HAVING to get rid of the quantity 0 records. I also calculated ONHANDQTY very simply using POSTEDQTY, RECEIVED, and DEDUCTED only, whereas you might also consider PICKED and REGISTERED as per some AX reports.

Hope this helps.

WITH s AS ( SELECT s.[PARTITION], s.DATAAREAID, s.ITEMID, d.INVENTLOCATIONID, d.WMSLOCATIONID, SUM(s.POSTEDQTY + s.RECEIVED - s.DEDUCTED) AS ONHANDQTY FROM INVENTSUM s JOIN INVENTDIM d ON d.[PARTITION] = s.[PARTITION] AND d.DATAAREAID = s.DATAAREAID AND d.INVENTDIMID = s.INVENTDIMID WHERE s.CLOSEDQTY = 0 GROUP BY s.[PARTITION], s.DATAAREAID, s.ITEMID, d.INVENTLOCATIONID, d.WMSLOCATIONID HAVING SUM(s.POSTEDQTY + s.RECEIVED - s.DEDUCTED) <> 0 ) SELECT l.[PARTITION], l.DATAAREAID, l.INVENTLOCATIONID, l.WMSLOCATIONID, s.ITEMID, s.ONHANDQTY FROM WMSLOCATION l LEFT JOIN s ON s.[PARTITION] = l.[PARTITION] AND s.DATAAREAID = l.DATAAREAID AND s.INVENTLOCATIONID = l.INVENTLOCATIONID AND s.WMSLOCATIONID = l.WMSLOCATIONID

Was this reply helpful? Yes No