You’re offline. This is a read only version of the page.
Hello,
We use Dynamics AX 2009 at my company and I’m trying to create a sql query to get inventory values for a specific date. To check its results I use „Inventory value per item group” or „Inventory value by inventory dimension” standard status reports from AX 2009. In most cases, the values are equal, but there are always a few items that have different values from sql query result then from AX. This really only applies to a few items(for example we have approximately 1000 items in the database and only 15-20 has different inventory values from the sql result). I don’t know how can I solve this problem. Do you have any idea?
This is the sql query:
SELECT
itr.ITEMID,
SUM (itr.QTY) AS QTY,
SUM (CASE
WHEN itr.STATUSRECEIPT = 2 THEN itr.COSTAMOUNTPHYSICAL + itr.COSTAMOUNTADJUSTMENT
WHEN itr.STATUSRECEIPT = 1 THEN itr.COSTAMOUNTPOSTED + itr.COSTAMOUNTADJUSTMENT
WHEN itr.STATUSISSUE = 2 THEN itr.COSTAMOUNTPHYSICAL + itr.COSTAMOUNTADJUSTMENT
WHEN itr.STATUSISSUE = 1 THEN itr.COSTAMOUNTPHYSICAL + itr.COSTAMOUNTADJUSTMENT
ELSE 0 END) as INVENTVALUE,
CI.CURRENCYCODE
FROM dbo.INVENTTRANS AS itr
INNER JOIN .dbo.INVENTDIM ID on
itr.DATAAREAID = ID.DATAAREAID and
itr.INVENTDIMID = ID.INVENTDIMID
INNER JOIN dbo.COMPANYINFO CI on
itr.DATAAREAID = CI.DATAAREAID
WHERE
itr.DATAAREAID = 'x'
itr.STATUSISSUE in (0,1,2)
AND itr.STATUSRECEIPT in (0,1,2)
AND itr.DATEPHYSICAL <= '2019-02-28'
GROUP BY itr.ITEMID,CI.CURRENCYCODE
Thanks.
*This post is locked for comments
I have the same question (0)
Hi,
Have you checked the items/item transactions in AX2009 where you have a difference?
Is it possible that some kind of corrections have been made for those items where you see the difference?
Best regards,
Ludwig
Share
Report
All responses (
Answers (
4
