web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Mohammad R. Daoud / Historical Stock Status Script

Historical Stock Status Script

Views (4861)
Community Member Profile Picture Community Member

We been suffering from Historical Stock Status report issues when it comes to customers with big inventory transactions, I am providing a script that recalculate the historical stock status for your items based on the IV10200 and IV10201 tables, it will allow you to estimate your inventories using a historical date:

Historical Stock Sttus Summary
  1. DECLARE @ASOFDATE DATETIME
  2. SET @ASOFDATE = '2016-12-31'
  3.  
  4. SELECT
  5. ITEMNMBR AS [Item Number],
  6. TRXLOCTN AS [Location],
  7. SUM(Quantity) AS Quantity,
  8. SUM([Extended Cost]) AS [Total Cost]
  9.  
  10. FROM
  11.  
  12. (SELECT ITEMNMBR,
  13.  
  14. (dbo.IV10200.QTYRECVD -
  15. ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
  16.         WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
  17.         AND ITEMNMBR = IV10200.ITEMNMBR
  18.         AND DOCDATE <= @ASOFDATE), 0))
  19. AS [Quantity],
  20.         UNITCOST *
  21.             (dbo.IV10200.QTYRECVD -
  22.             ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
  23.             WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
  24.             AND ITEMNMBR = IV10200.ITEMNMBR
  25.             AND DOCDATE <= @ASOFDATE), 0))
  26. AS [Extended Cost],             
  27. TRXLOCTN
  28. FROM dbo.IV10200 WHERE DATERECD <@ASOFDATE) AS SOURCE
  29. GROUP BY ITEMNMBR, TRXLOCTN

Hope that his helps!

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo


This was originally posted here.

Comments

*This post is locked for comments