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

at

12 comments:

  1. AnonymousMay 2, 2013 at 4:45 PM

    Thank you very much! This is very helpful.

    Cheers!

    ReplyDelete
  2. AnonymousMay 14, 2013 at 11:11 AM

    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?

    ReplyDelete
  3. GradyMay 15, 2013 at 11:01 AM

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

    ReplyDelete
  4. Mohammad R. DaoudMay 19, 2013 at 5:54 AM

    Hello Grady,

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

    ReplyDelete
  5. SecretGeekNovember 11, 2014 at 3:13 AM

    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!

    ReplyDelete
  6. LaineFebruary 9, 2015 at 1:04 PM

    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.

    ReplyDelete
  7. Mohammad R. DaoudFebruary 9, 2015 at 11:55 PM

    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.

    ReplyDelete
  8. UnknownMarch 12, 2015 at 7:53 AM

    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

    ReplyDelete
  9. UnknownNovember 3, 2015 at 7:42 AM

    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)

    ReplyDelete
  10. Emy WatsonDecember 13, 2015 at 11:00 PM

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

    ReplyDelete
  11. AlmdinaNovember 30, 2017 at 4:08 AM

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

    ReplyDelete
  12. Mohammad R. DaoudJanuary 28, 2018 at 12:02 AM

    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

    ReplyDelete