Select On-hand Per Date In AX 2012 Using SQL Query

If you know database structure in AX 2012, you can directly select data from SQL Server. Of course select data from SQL Server are faster than select data from AX 2012 framework.

In AX 2012, you can run report inventory to get On-hand data. To access it,  use this navigation : Inventory management -> Reports -> Physical inventory -> Physical inventory by inventory dimension. Use parameter below to run report :

onhand_axpar

and this is the result :

onhand_ax

You can use query below to produce report like picture above.

use [MicrosoftDynamicsAX];

declare @asOnDate varchar(10) = '2017/02/21'
declare @DataAreaID varchar(10) = 'usmf'
declare @ItemId varchar(20) = 'M0010'
declare @Size varchar(20) = '200'

Select onHand.ITEMID, onHand.PRODUCTNAME, onHand.UNITID, onHand.ITEMGROUPID, 
 onHand.INVENTSIZEID, onHand.INVENTCOLORID,
 case 
 when oBal.OPBAL_QTY is null then 0
 else oBal.OPBAL_QTY
 end as BALANCEQTY
from 
(
 select a.ITEMID, b.PRODUCTNAME, c.UNITID, d.ITEMGROUPID, e.INVENTSIZEID, e.INVENTCOLORID
 from INVENTTABLE as a
 left join ECORESPRODUCTTRANSLATIONS b on a.PRODUCT = b.PRODUCT
 left join INVENTTABLEMODULE c on a.ITEMID = c.ITEMID and c.MODULETYPE = 0
 left join INVENTITEMGROUPITEM d on a.ITEMID = d.ITEMID and a.DATAAREAID = d.ITEMDATAAREAID
 inner join 
 (
 select iSum.ITEMID, iDim.INVENTSIZEID, iDim.INVENTCOLORID
 from INVENTSUM as iSum 
 inner join INVENTDIM as iDim on iDim.INVENTDIMID = iSum.INVENTDIMID
 where iSum.DATAAREAID = @DataAreaID and iDim.DATAAREAID = @DataAreaID 
 group by iSum.ITEMID, iDim.INVENTSIZEID, iDim.INVENTCOLORID
 ) as e on e.ITEMID = a.ITEMID
 where a.DATAAREAID = @DataAreaID and c.DATAAREAID = @DataAreaID and a.ITEMID = @ItemId and
 e.INVENTSIZEID = @Size
) as onHand

--Get opening balance
left join 
(
 Select iTrans.ITEMID, iDim.INVENTSIZEID, iDim.INVENTCOLORID, sum(iTrans.QTY) as OPBAL_QTY
 from INVENTTRANS as iTrans 
 inner join INVENTDIM as iDim on iDim.INVENTDIMID = iTrans.INVENTDIMID
 where iTrans.DATAAREAID = @DataAreaID and iDim.DATAAREAID = @DataAreaID and
 ((iTrans.DATEINVENT <> '' and iTrans.DATEINVENT <= @asOnDate) OR
 (iTrans.DATEINVENT = '' and iTrans.DATEPHYSICAL <> '' and iTrans.DATEPHYSICAL <= @asOnDate))
 group by iTrans.ITEMID, iDim.INVENTSIZEID, iDim.INVENTCOLORID
) as oBal on oBal.ITEMID = onHand.ITEMID and 
 oBal.INVENTSIZEID = onHand.INVENTSIZEID and obal.INVENTCOLORID = onHand.INVENTCOLORID

If you execute query above in Microsoft SQL Server Manajement Studio (MSSMS), you will see result like a picture below.

onhand_sql

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: