Open Forum

  • 1.  Inventory QTYONHND Field

    Posted Sep 23, 2021 03:18 PM
      |   view attached
    I am trying to figure out what/where I am missing on Inventory

    I have to update 5200 records to change the QTY on hand to a zero amount. I ran the following SQL statement - The table show zero, and the smartlist by location ID shows zero - but this screen shot doesn't show it as zero - what I am missing?? I also went in for fun (all on my test server) and updated the qtyonhnd to 156 and it shows that in SQL and in smartlist but not on the attachment for item maintenance

    This was the SQL statement I ran to update 1 table
    update IV00102 set QTYONHND = '0' where locncode = 'warehouse' and QTYONHND > '0'

    Angela Harris
    Territorial Financial Systems Administrator
    The Salvation Army-Central THQ
    Hoffman Estates IL
    Academy - Online Interactive Learning from Experts

  • 2.  RE: Inventory QTYONHND Field

    Posted Sep 24, 2021 01:10 AM

    Hello Angela,
    If you query an individual part from the IV00102 table you should see multiple rows for each of your locations, and another row which is the All Locations row (which has a RCRDTYPE = 1). This row contains the totals for all of your locations combined. The All Locations row is what shows in the Quantity On Hand field on the Item Master window.

    So depending on if you have multiple locations or not will depend on how you proceed. The proper way to proceed would probably be to Sum for RCRDTYPE = 2 and then update the row with RCRDTYPE = 1. So if you have 2 locations, 1 with QTYONHND = 1 and the other with QTYONHND = 2 then you would update the All Locations row with QTYONHND = 3.

    If you only have the 1 location then you would want to use your update query and also do something like this:

    update IV00102
    set QTYONHND = 0
    where RCRDTYPE = 1 and LOCNCODE = '' and QTYONHND > 0

    You probably don't need the LOCNCODE = '' but I like my update queries to be very specific.

    James Bacon
    Green Rubber - Kennedy Ag.

    Academy - Online Interactive Learning from Experts

  • 3.  RE: Inventory QTYONHND Field

    Posted Sep 24, 2021 07:39 AM
    Hi Angela,
    I'm not sure of the end goal or if you are clearing activity, but typically you don't want to directly update the quantity on hand field in the IV00102 table.  You should import an inventory adjustment to offset the current on hand amount and post that.  If there are actual on hand quantities then there are amounts remaining in your cost layer tables.  IV10200 and IV10201.  An inventory reconcile would likely reset your on hand back to the original amount.

    Andrew Kennedy
    Principal Consultant
    Seven Hills OH

    Academy - Online Interactive Learning from Experts

  • 4.  RE: Inventory QTYONHND Field

    Posted Sep 24, 2021 07:46 AM
    Hi @Angela Harris

    Since you ran this in SQL and not the User Interface you'll need to run Inventory Reconcile to get the displays fixed.​

    Jo deRuiter
    💫Granite Award Winner
    Atlanta Chapter Leader
    Aisling Dynamics Business Solutions
    Business Applications Practice Manager

    Academy - Online Interactive Learning from Experts

  • 5.  RE: Inventory QTYONHND Field

    Posted Sep 24, 2021 08:53 AM
    Did you try running a check links or a reconcile on inventory items?

    Diane Hays, CPA, MBA
    Senior Consultant
    Alta Vista Technology LLC
    Chippewa Falls WI

    Academy - Online Interactive Learning from Experts

If you've found this thread useful, dive deeper into User Group community content by role