Hi all,
We have some issues with the transactions in inventtrans back in time. Thus, we want to calculate inventory levels back in time by using INVENTSUM as the main table, and then use the use INVENTTRANS and QTY to calculate back to a given date.
Does anyone have a query that I could use for inspiration?
This is what I am trying, but I get a wrong answer;
SELECT
upper(isum.[DATAAREAID]) as CompanyKey
,isum.[ITEMID] as ItemKey
,sum (
(isum.[POSTEDQTY]+isum.[RECEIVED]-isum.[DEDUCTED]-isum.[PICKED])
+ itns.[QTY]
) as InventorySnapshotOnHandQty
,(
sum (
(isum.[POSTEDQTY]+isum.[RECEIVED]-isum.[DEDUCTED]-isum.[PICKED])
+ itns.[QTY]
)
* invm.[PRICE]
)
as InventorySnapshotOnHandAmountLCY
FROM
[dbo].[AX2009_dbo_INVENTSUM_V] isum
LEFT JOIN
[dbo].[AX2009_dbo_INVENTTRANS_V] itns
on isum.DATAAREAID=itns.DATAAREAID and isum.INVENTDIMID=itns.INVENTDIMID and isum.INVENTDIMID=itns.INVENTDIMID and isum.ITEMID=itns.ITEMID
LEFT JOIN
[dbo].[AX2009_dbo_INVENTDIM_V] idim
on isum.DATAAREAID=idim.DATAAREAID and isum.INVENTDIMID=idim.INVENTDIMID
LEFT JOIN
[dbo].[AX2009_dbo_INVENTTABLEMODULE_V] invm
on isum.DATAAREAID=invm.DATAAREAID and isum.ITEMID=invm.ITEMID and invm.MODULETYPE=0
WHERE
isum.DATAAREAID = 'COMPANYID' AND -- ENTER COMPANY
isum.ITEMID = 'ITEMDIM' and isum.[LASTUPDDATEEXPECTED] <= {ts '2016-11-30 00:00:00.000'} -- ENTER ITEMID AND DATE
GROUP BY
isum.[DATAAREAID]
,isum.[ITEMID]
,invm.[PRICE]
When I just calculate the inventory based on inventtrans I get almost the correct number with the following query.
SELECT
upper(itns.[DATAAREAID]) as CompanyKey
,itns.[ITEMID] as ItemKey
,sum(itns.[QTY]) as InventoryTransactionQty
,sum(itns.[COSTAMOUNTPOSTED]+itns.[COSTAMOUNTADJUSTMENT]) as InventoryTransactionAmountLCY
FROM
[dbo].[AX2009_dbo_INVENTTRANS_V] itns
LEFT JOIN
[dbo].[AX2009_dbo_INVENTDIM_V] idim
on itns.DATAAREAID=idim.DATAAREAID and itns.INVENTDIMID=idim.INVENTDIMID
LEFT JOIN
[dbo].[AX2009_dbo_INVENTTABLEMODULE_V] invm
on itns.DATAAREAID=invm.DATAAREAID and itns.ITEMID=invm.ITEMID and invm.MODULETYPE=0
WHERE
itns.DATAAREAID = 'COMPANYKEY' AND -- ENTER LEGAL ENTITY ID
itns.ITEMID = 'ITEMID' and itns.DATEPHYSICAL <= {ts '2016-11-30 00:00:00.000'}
GROUP BY
itns.[DATAAREAID]
,itns.[ITEMID]
But as I said, we have some issues with data back in time. Thus it is decided to use INVENTSUM and calculate back in time.
Someone who has I query for inspiration?
Bests,
Line, Denmark
