* Inventory - Bulk Adjustments

Objects in use

• frmAdjustInventoryWizard (FIRST FORM)
• frmAdjustInventory
• frmAdjustInventorySub
• 
  

Items selection for the Inventory Bulk Adjustment (Last Edited PW 17.0816 SR26959)

MakeAdjustments ( PostDateTo ) 

• Popup "Are you sure you want to permanently update the inventory at this time?" 
• IF YES 
• IF Adjustments_Exist is YES 
• Get Username , Division Code, Inventory GL, Cost Of Goods Sales GL
• Get HandleZeroAdjustments from CSP
• Get blnForceBO = EnableBackOrdering from CSP
• Open Recordset SELECT tblAdjustInventoryTemp.AdjustToQuantity, tblAdjustInventoryTemp.[Item Number], tblAdjustInventoryTemp.[Quantity in Stock],[QuantityUnshipped], [Warehouse Code], (nz([tblAdjustInventoryTemp].[AdjustToQuantity],0)-[tblAdjustInventoryTemp].[QuantityUnshipped]) AS Diff, Inventory.[Cost from Mfg] FROM tblAdjustInventoryTemp INNER JOIN Inventory ON tblAdjustInventoryTemp.[Item Number] = Inventory.[Item Number] WHERE tblAdjustInventoryTemp.AdjustToQuantity Is Not Null
• WHILE NOT rs.EOF
• AdjQty = rs!Diff 
• IF HandleZeroAdjustments = 1 (if HandleZeroAdjustments = 1 then do not take into account Unshipped and special orders)
• AdjQty = AdjQty + rs!QuantityUnshipped
• TotCost = AdjQty * rs![Cost from Mfg]
• If AdjQty <> 0 Then
• CleanUpSpecialOrders Item#
• Select Case AdjQty
• Case 0 
• IF rs!QuantityUnshipped = 0 And rs!AdjustToQuantity = 0 
• Open RecordSet rsItemShipments from [Inventory Products] for [Item Number]=rs!Item#, and SalesOrderLineNumber=0 and [Warehouse Code]=rs![Warehouse Code]
• Do While Not rsItemShipments.EOF
• Set [Quantity In Stock] = 0 
• Delete Record
• Log in the InventoryProductsLog
• RecSet Move Next
• Loop
• Case > 0
• Clear Existing Shipments
• Open RecordSet rsItemShipments from [Inventory Products] for [Item Number]=rs!Item#, and SalesOrderLineNumber=0 and [Warehouse Code]=rs![Warehouse Code]
• Do While Not rsItemShipments.EOF
• Set [Quantity In Stock] = 0 
• Delete Record
• Log in the InventoryProductsLog
• RecSet Move Next
• Loop
• IF rs![Quantity In Stock] < 0 
• Add_To_Inventory rs("Item Number"), Abs(rs![Quantity In Stock]), Nz(rs("Cost From MFG"), 0), , , , Nz(rs![Warehouse Code], ""), , True, "Stock Adjustments made from Inventory Adjustment form on " & Date & " by " & Username & "", LogSource:="Inventory Adjustments Screen"
• Add_To_Inventory rs("Item Number"), Abs(AdjQty + rs![Quantity In Stock]), Nz(rs("Cost From MFG"), 0), , , , Nz(rs![Warehouse Code], ""), , True, "Stock Adjustments made from Inventory Adjustment form on " & Date & " by " & Username & "", LogSource:="Inventory Adjustments Screen" (No Idea why we add items twice)
• Add_To_Inventory_Adjustment_Queue (add to Adjustment GL Posting)
• ELSE
• Add_To_Inventory rs("Item Number"), AdjQty, Nz(rs("Cost From MFG"), 0), , , , Nz(rs![Warehouse Code], ""), , True, "Stock Adjustments made from Inventory Adjustment form on " & Date & " by " & Username & "", LogSource:="Inventory Adjustments Screen"
• Add_To_Inventory_Adjustment_Queue rs("Item Number"), TotCost, AdjQty, "Physical Count Adjustment", , , , DiveCOSGL, DiveInvGL, DiveCode
• Case < 0
• AdjingQty = AdjQty
• Clear Existing Shipments
• Open RecordSet rsItemShipments from [Inventory Products] for [Item Number]=rs!Item#, and SalesOrderLineNumber=0 and [Warehouse Code]=rs![Warehouse Code]
• Do While Not rsItemShipments.EOF
• Set [Quantity In Stock] = 0 
• Delete Record
• Log in the InventoryProductsLog
• RecSet Move Next
• Loop
• IF blnForceBO = False 
• Add_To_Inventory rs("Item Number"), AdjQty, Nz(rs("Cost From MFG"), 0), , , , Nz(rs![Warehouse Code], ""), , True, "Stock Adjustments made from Inventory Adjustment form on " & Date & " by " & Username & "", LogSource:="Inventory Adjustments Screen"
• Log in the InventoryProductsLog
• Add_To_Inventory_Adjustment_Queue rs("Item Number"), TotCost, AdjQty, "Physical Count Adjustment", , , , DiveCOSGL, DiveInvGL, DiveCode
• End Select
• IF HandleZeroAdjustments = 1 then 
• Delete all remaining inventory products records where [Item Number] =  rs![Item Number]  and nz([SalesOrderLineNum],0)= 0 and [Warehouse Code]= Nz(rs![Warehouse Code], "") 
• RecSet Move Next
• End While
• Recount all inventory Quantities and update Inventory table
• Select Case PostDateTo
• Case "All"
• UPDATE [Inventory] SET [AdjustToQuantity] = NULL, [Last Inventory Date]= ToDate 
• UPDATE [tblAdjustInventoryTemp] SET [AdjustToQuantity] = NULL
• Case "AdjustedOnly"
• UPDATE Inventory INNER JOIN tblAdjustInventoryTemp ON Inventory.[Item Number] = tblAdjustInventoryTemp.[Item Number]  SET Inventory.AdjustToQuantity = Null, Inventory.[Last Inventory Date] = Date WHERE (Not ([tblAdjustInventoryTemp].[AdjustToQuantity]) IS Null)
• UPDATE [tblAdjustInventoryTemp] SET [AdjustToQuantity] = NULL
• End Select
• Popup Message "Inventory counts have been adjusted."
• FillTempTable_New (To Select data for Temp Table)
• ELSE
• Popup MsgBox "There do not appear to be any adjustments that need to be made at this time.  To adjust the actual quantity in stock, fill in the 'Actual Qty' field with the correct quantity and try pressing this button again."
• 
  

Calling Sub FillTempTable_New

• Delete temp table tblAdjustInventoryTemp
• If Form frmAdjustInventoryWizard is loaded then select DefaultWH from that Wizard
• Else select DefaultWH from Adjust Inventory Warehouse dropdown list
• Else select Main warehouse as DefaultWH 
• Append all Active and Stock type Items To Temp Table tblAdjustInventoryTemp
• Update Quantity Unshipped in tblAdjustInventoryTemp temp table from tblUnshippedInventory
• Create Temp table tblAdjustInventoryWHTemp with qryInventoryByWarehouseAll for Item Warehouse Quantities
• Update tblAdjustInventoryTemp with Item Warehouse Quantities in temp table tblAdjustInventoryWHTemp 
• Delete Temp table tblAdjustInventoryWHTemp 
• Create Temp table tblAdjustInventoryWHTemp with qryInventoryByWarehouseSummary for Non warehouse items
• Update tblAdjustInventoryTemp with Item Non -Warehouse Quantities in temp table tblAdjustInventoryWHTemp 
• Delete Temp table tblAdjustInventoryWHTemp