Microsoft Dynamics AX (Archived)

query for physical value amount as of date

(0) Share

Report

Posted on by Community Member

Dear all,

Does any one know how to get physical value amount in inventtrans as of date ?

I try to query in inventtrans and compare with the inventory value. the amount always not tally.

Thanks

Regards

Mulyadi

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Prashant Singh 8,802 on at

Like (0)

Report

RE: query for physical value amount as of date

You can get this from report also.

1. Physical Inventory by inventory dimension.

You need to understand the entries in the inventtrans

blog.mohamedaamer.com/.../inventory-management-inventory-descriptions

check all type of entries and reconcile your report again.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: query for physical value amount as of date

Hi Kumar,

What i mean is by query sql. any one can provide me the query sql how to calculate the physical amount.

Thanks

Was this reply helpful? Yes No
Suggested answer

Prashant Singh 8,802 on at

Like (0)

Report

RE: query for physical value amount as of date

I have done this reconcile and any SQL query is not required in this. You just need to work on excel thats so simple.

Was this reply helpful? Yes No
Suggested answer

Guy Terry 28,890 Moderator on at

Like (0)

Report

RE: query for physical value amount as of date

Hi Mulyadi,

This SQL will give you what I would call the value of your on-hand, and can be backdated.

You need to enter your Company ID, an Item ID and a Date.

SELECT

ITr.ITEMID AS 'Item Id',

SUM (ITr.QTY) AS Quantity,

SUM (CASE

WHEN ITr.STATUSISSUE = 2 THEN ITr.COSTAMOUNTPHYSICAL

WHEN ITr.STATUSRECEIPT = 2 THEn ITr.COSTAMOUNTPHYSICAL

ELSE ITr.COSTAMOUNTPOSTED + ITr.COSTAMOUNTADJUSTMENT

END) AS 'Cost value'

FROM dbo.INVENTTRANS AS ITr

WHERE ITr.DATAAREAID = 'USMF' AND -- ENTER LEGAL ENTITY ID

ITr.STATUSISSUE <=2 AND

ITr.STATUSRECEIPT <=2 AND

ITr.ITEMID = 'MYITEM' and ITr.DATEPHYSICAL <= {ts '2015-06-17 00:00:00.000'} -- ENTER ITEMID AND DATE

GROUP BY ITr.ITEMID

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: query for physical value amount as of date

do you know how i can get the financial quantity?

Was this reply helpful? Yes No
Guy Terry 28,890 Moderator on at

Like (0)

Report

RE: query for physical value amount as of date

Using my SQL? In the WHERE part of the statement, change STATUSISSUE and STATUSRECEIPT to less than or equal to 1.

Was this reply helpful? Yes No