Monday, April 22, 2013

Historical Stock Status Script

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

12 comments:

Anonymous said...

Thank you very much! This is very helpful.

Cheers!

Anonymous said...

I think you have typo on the final @AsOfDate criteria. The other 2 where clauses use <= @AsOfDate but the final one uses only less than instead of less than and equal to. Correct?

Grady said...

I do not see any unit of measure calculations. Does that mean this script assumes all quantities sold are of the same unit?

Mohammad R. Daoud said...

Hello Grady,

This brings the data calculated for the minimum unit of measurement (Pieces).

SecretGeek said...

Hi Mohammed. I just tried using this script and from the 3000 items i have on GP 2010 about 600 of them were incorrect. I was double checking the query by putting today's date and comparing them to the current item quantities.

Any suggestions on how to fix this? Thanks in advance!

Laine said...

I having the same issue as SecretGeek. I've run your script and I'm getting items that show 0 quantity as of 12/31/2014 but I know that there was a quantity on hand as of 12/31/14.

Mohammad R. Daoud said...

Hello Guys,

This was tested on a limited data and therefore you might be having cases not covered in my query, this was tested by several users so far and none reported any mistakes, please provide any insights to help accordingly.

Unknown said...

Mr. Mohamed
Many thanks for a very useful query I faced this issue the Historical report show wrong data but your query show the right data do you have any idea how can fix the Historical report

Unknown said...

you can not depend on docdate on these tables, if so it will give wrong results,
in some case related to cost change the date will deffer than the origional document in iv3030
if you take the date from iv30300 it will be correct, else table see30303 may give realistic results (depends on the integrity of the table)

Emy Watson said...

How to trade in stock market from home?
Magento Quick View

Almdina said...

يمكنكم الحصول على مصدر ماء نظيف وذلك من خلال الإعتماد على شركة المدينة 0503644871 أفضل شركة غسيل خزانات بالمدينة المنورة نظراً إلى حرصها الدائم على اقتناء أحدث معدات تنظيف الخزانات كما تستخدم شركة المدينة مواد غسيل خزانات آمنة تماما على صحة الفرد والمجتمع ومصرح بإستخدامها من قبل وزارة الصحة بالمملكة العربية السعودية

Mohammad R. Daoud said...

Updated script including some changes if the data is having duplicates in RCTSEQNMBR:

SELECT
SOURCE.ITEMNMBR AS [Item Number],
ITEMDESC,
TRXLOCTN AS [Location],
SUM(Quantity) AS Quantity,
SUM([Extended Cost]) AS [Total Cost]

FROM

(

SELECT ITEMNMBR,
RCTSEQNM,
(SUM(dbo.IV10200.QTYRECVD) -
ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
AND ITEMNMBR = IV10200.ITEMNMBR
AND DOCDATE <= '2017-12-31'), 0))
AS [Quantity],
AVG(UNITCOST) *
(sum(dbo.IV10200.QTYRECVD) -
ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
AND ITEMNMBR = IV10200.ITEMNMBR
AND DOCDATE <= '2017-12-31'), 0))
AS [Extended Cost],
TRXLOCTN
FROM dbo.IV10200 WHERE DATERECD <'2017-12-31'
Group By ITEMNMBR, RCTSEQNM, TRXLOCTN

) AS SOURCE

INNER JOIN IV00101 ON SOURCE.ITEMNMBR = IV00101.ITEMNMBR
GROUP BY SOURCE.ITEMNMBR, TRXLOCTN, ITEMDESC
Order by SOURCE.ITEMNMBR

Related Posts:

Related Posts with Thumbnails