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
- DECLARE @ASOFDATE DATETIME
- SET @ASOFDATE = '2016-12-31'
- SELECT
- ITEMNMBR AS [Item Number],
- TRXLOCTN AS [Location],
- SUM(Quantity) AS Quantity,
- SUM([Extended Cost]) AS [Total Cost]
- FROM
- (SELECT ITEMNMBR,
- (dbo.IV10200.QTYRECVD -
- ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
- WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
- AND ITEMNMBR = IV10200.ITEMNMBR
- AND DOCDATE <= @ASOFDATE), 0))
- AS [Quantity],
- UNITCOST *
- (dbo.IV10200.QTYRECVD -
- ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
- WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
- AND ITEMNMBR = IV10200.ITEMNMBR
- AND DOCDATE <= @ASOFDATE), 0))
- AS [Extended Cost],
- TRXLOCTN
- FROM dbo.IV10200 WHERE DATERECD <@ASOFDATE) AS SOURCE
- 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:
Thank you very much! This is very helpful.
Cheers!
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?
I do not see any unit of measure calculations. Does that mean this script assumes all quantities sold are of the same unit?
Hello Grady,
This brings the data calculated for the minimum unit of measurement (Pieces).
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!
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.
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.
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
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)
How to trade in stock market from home?
Magento Quick View
يمكنكم الحصول على مصدر ماء نظيف وذلك من خلال الإعتماد على شركة المدينة 0503644871 أفضل شركة غسيل خزانات بالمدينة المنورة نظراً إلى حرصها الدائم على اقتناء أحدث معدات تنظيف الخزانات كما تستخدم شركة المدينة مواد غسيل خزانات آمنة تماما على صحة الفرد والمجتمع ومصرح بإستخدامها من قبل وزارة الصحة بالمملكة العربية السعودية
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
Post a Comment