How to Fix Inventory Qty on Hand with LOT Quantity for Sage 300 Inventory

There are 2 tables in Sage 300 LOT Inventory:

• Qty on Hand
• LOT Quantity for Inventory LOT Items.

There are times when the Qty and Inventory Value do not match on both screens. To see the values for Qty on Hand, the Inventory Valuation Report can be generated for all transactions. To see LOT Quantity, that can be seen during Inventory Adjustments when you allocate the LOT or from Serial/LOT Reconciliations in the Sage 300 IC Periodic Processing folder.

Note: Inventory Valuation is by Item and Location. Therefore, if you have multiple locations, the avg cost will be different in each location unless the cost is exactly the same by location.

Possible Resolution:
When both tables are not in sync and normal Inventory Adjustments can’t fix the QTY and VALUE for an item, one method that could be used is to zero out the Qty on Hand and VALUE for both tables. Once they are at a zero balance, we would then enter in the new beginning balances for Qty and VALUE.

To do this:

1. Run Day End Processing
2. Check to see if the quantities match for the Inventory Item tables (Qty on Hand and LOT Quantity).

                a. Run the Sage 300 Serial/LOT Number Qty on Hand Report.

                b. Go to: Inventory Control / IC Stock Control Reports / Serial/LOT Number Qty On                      Hand

                c. Be sure to pick LOT

                d. Checkmark Show only items with IC Quantity Variance

                e. The report will list out all of the QTY Variances between the 2 Tables.

       3. If the report shows no discrepancies, skip this section. If there are discrepancies:

                a. Go to: Inventory Control / IC Periodic Processing / Serial/LOT Reconciliations

                b. You want to match to the Qty on Hand on the IC Serial/LOT Number Qty On                             Hand.

                           i. To Increase LOT Quantity select Receipt.

                           ii. To Decrease LOT Quantity select Shipment.

                c. Post Transactions

                d. Please note that making transactions here only affects the Lot Quantity Data                          Table, not the Qty on Hand Data Table.

                e. It also does not make any journal entries and it will not create any financial                              transactions.

      4. Once the quantities match for the Item on the Qty on Hand and LOT Quantity tables, let’s zero out the Inventory Qty on Hand and LOT Quantity tables.

                 a. Before doing this, print out your Items with the Qty for each LOT since we will                          need to add them back in.

                 b. Run the Item Valuation Report to see your Qty on Hand

                 c. To clear out the Qty and the Value, we will process an Inventory Shipment.

                 d. Go to: Inventory Control / IC Transactions / Shipments

                 e. The reason why you want to use SHIPMENTS instead of Adjustments is that it                        will ship out the qty and total value of the item for both tables (Qty on Hand                            and LOT Quantity). Adjustments do not seem to clear out the LOT Quantity                              table for this situation.

                 f. Ship all of the quantity for that item so that the Qty on Hand becomes zero.

                 g. Be sure to zero out the Unit Price so it does not record a sale.

                 h. Post Transactions

                 i. Run Day End Processing

                 j. The Qty on Hand should zero on the Inventory Valuation Report.

                 k. Please note that making regular Inventory Transactions (Receipts, Shipments,                         Adjustments, etc.) will update both Inventory LOT Tables (Qty on Hand and                             LOT Quantity)

                 l. This will create financial transactions.

       5. Check to see if the Qty on Hand is zero. If there is an Actual Cost Value for the                        location, you will need to make an Inventory Cost Adjustment.

                 a. Go to: Inventory Control / IC Transactions / Adjustments

                 b. Enter transaction by selecting Cost Increase or Cost Decrease and entering                             Cost Adjustment Amount

                 c. Post Transactions

                 d. Run Day End Processing

                 e. Verify that the Qty and Actual Cost Amount are zero in the Inventory Valuation                       Report

          6. Process an Inventory Receipt to bring back the Qty and Value for both tables.

                 a. Process an Inventory Receipt to bring back Qty and Value.

                 b. Go to: Inventory Control / IC Transactions / Receipts

                 c. Enter in the LOT Qtys (From Step 4a) and the new Unit Cost.

                 d. Post Transactions

                 e. Process Day End Processing

                 f. Both tables should now match (Inventory Valuation Report and LOT Quantities)

         7. There may be times where the LOT Quantity Table has some transactions where                    the Cost for Costing amount still exists. We need to remove them to be in sync                      with the Qty on Hand table (Inventory Valuation Report)

                 a. Go to: Inventory Control / IC Periodic Processing / Serial/Lot Reconciliations

                 b. If Cost of Costing amount exists with no Quantity for Costing, you may need to                       do a Receipt of 1 to increase the Qty so you can then do a Shipment to remove                       it with the Cost for the LOT.

                 c. Post Transactions

                 d. Transactions here will not make any financial impact and only update the LOT                         Quantity table, not Inventory Valuation (Qty on Hand table)

                 e. Check to see if everything matches at the Item and Location level