CODEWITHSUNDEEP
axaptadynamics-ax-2012x++dynamics-ax-2012-r3inventory-management
user2184523
user2184523

Reputation: 41

A SQL query to find all wmsLocations that have zero stock in AX 2012 R3

I have been trying this for a day now, and I can't seem to find the correct way to do this. I need a SQL query that returns me all locations with zero physical stock ( inventSum.PostedQty + inventSum.Received - inventSum.Deducted + inventSum.Registered - inventSum.Picked <= 0) I think it should be as simple as below but this is returning me locations where ( inventSum.PostedQty + inventSum.Received - inventSum.Deducted + inventSum.Registered - inventSum.Picked > 0). Can someone please help me figure out what is wrong here?

select
wmslocationid
from wmsLocation
order by wmsLocation.wMSLocationId
where
(wmsLocation.inventLocationId == inventLocationId) //default warehouse
exists join inventDim
where (inventDim.InventSiteId == inventSiteId) &&//default site
(inventDim.InventLocationId == inventLocationId) &&
(inventDim.WMSLocationId == wmsLocation.wMSLocationId)
exists join inventSum
where (inventDim.InventDimId == inventSum.InventDimId) &&
(inventSum.PostedQty + inventSum.Received - inventSum.Deducted + inventSum.Registered - inventSum.Picked <= 0);

Upvotes: 0

Views: 1315

Answers (2)

user2184523
user2184523

Reputation: 41

I tried it but it still gave me all the records. When I try generateonly and forceliteral, it looked like the sql query is correct but the result data was still giving me unrelated values. I ended up fixing it by creating an AOT Query object. The solution is very long so I have added it to blog https://locus90.blogspot.co.uk/2018/05/a-sql-query-to-find-all-wmslocations.html.

In a nutshell I ended up fixing it by creating an AOT Query object With wmsLocations where I am grouping by location ID, inner join with InventDim but InventDim has an outer Join with InventSum and having a view on the query where syscomputed column on it finds the physical Stock for that group of wmsLocations which we can use as a range in another query giving us all locations. This only works with inner join, so another non exist join was required for every remaining location without inventdim.

Thanks again for all your help, and let me know if you can recommend a simple solution for this.

Upvotes: 0

10p
10p

Reputation: 6778

Some items can have zero physical stock while other items can have positive physical stock in the same location, have you considered that? Do you also need to filter by items? If you need to find locations where all items have zero stock then try using notexists instead of exists.

I have nowhere to test it at the moment, but you can try replacing

exists join inventSum
where (inventDim.InventDimId == inventSum.InventDimId) &&
(inventSum.PostedQty + inventSum.Received - inventSum.Deducted + inventSum.Registered - inventSum.Picked <= 0);

from your statement with

notexists join inventSum
where (inventDim.InventDimId == inventSum.InventDimId) &&
(inventSum.PostedQty + inventSum.Received - inventSum.Deducted + inventSum.Registered - inventSum.Picked > 0);

Also, you could try

select wmslocationid from inventDim
    group by wmslocationid
    where inventDim.InventSiteId        == inventSiteId         //default site
       && inventDim.InventLocationId    == inventLocationId     //default warehouse
notexists join inventSum   
    where inventSum.InventDimId == inventDim.InventDimId
       && inventSum.Closed      == NoYes::No
       && inventSum.PostedQty + inventSum.Received - inventSum.Deducted + inventSum.Registered - inventSum.Picked > 0;

Upvotes: 2

Related Questions