Explanation of SQL
The following query will locate and repair any discrepancies between the On Order amount in inventory and the total on order in P.O.s.
NOTE:
If you execute this query with @CommitChanges = 0, it will NOT apply any changes and only display a list of the inventory items with discrepancies.
Executing this query with @CommitChanges = 1 will apply the changes. This query will add returns to the InventoryLog and Journal tables.
After executing it, the following is required to ensure your inventory levels are corrected.
- Execute the Inventory Out of Balance Repair Query.
- Restart Chapi and the SSLIP.
Risk of Data Corruption if Run Improperly
High. Inventory data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.
SQL Query
DECLARE @CommitChanges BIT; SET @CommitChanges = 0; DECLARE @EntryDate DateTime; SET @EntryDate = GetDate(); DECLARE @Description VARCHAR(255); SET @Description = 'Inventory OnOrder Adjustment by Cyrious'; DECLARE @Notes VARCHAR(MAX); SET @Notes = 'Inventory OnOrder Adjustment by Cyrious'; DECLARE @ModifiedDate DateTime; SET @ModifiedDate = GetDate(); DECLARE @ModifiedByUser VARCHAR(100); SET @ModifiedByUser = '(cyrious)'; DECLARE @ModifiedByComputer VARCHAR(100); SET @ModifiedByComputer = '(cyrious)'; DECLARE @T TABLE(InventoryID INT, PartID INT, DivisionID INT, WarehouseID INT, CurrentOnOrder FLOAT, ActualOnOrder FLOAT, New_JournalID INT ) ; -- Insert all of the inventory items into the temp table INSERT INTO @T (InventoryID, PartID, DivisionID, WarehouseID) SELECT I.ID, I.PartID, I.DivisionID, I.WarehouseID FROM Inventory I LEFT JOIN Part P ON P.ID = I.PartID WHERE P.PartType = 0 AND I.IsGroup = 0 ; -- Set the current inventory items UPDATE @T SET CurrentOnOrder = ISNULL( IL.QuantityOnOrder, 0 ) FROM @T T LEFT JOIN ( SELECT InventoryID , ROUND( SUM( ISNULL( QuantityOnOrder, 0 ) ), 4 ) AS QuantityOnOrder FROM Inventorylog WHERE InventoryID IS NOT NULL GROUP BY InventoryID ) IL ON IL.InventoryID = T.InventoryID ; -- Compute the actual on order quantity and set the value DECLARE @ActualOnOrder TABLE (InventoryID INT PRIMARY KEY, Amount FLOAT); INSERT INTO @ActualOnOrder SELECT I.ID AS InventoryID , SUM( CASE WHEN TD.UnitID = P.UnitID THEN TD.POQuantityOnOrder ELSE ( TD.POQuantityOnOrder * ISNULL(IC.FxUpper / IC.FxLower, 1 ) ) END ) POQuantityOnOrder FROM ( SELECT TransHeaderID , (CASE WHEN ItemClassTypeID = 12076 THEN (SELECT TOP 1 PartID FROM CatalogItem WHERE ID = ItemID) ELSE ItemID END ) PartID , UnitID , Quantity , BillQuantity , (ISNULL( Quantity, 0 ) - ISNULL( BillQuantity, 0 ) ) AS POQuantityOnOrder , WarehouseID FROM VendorTransDetail WHERE ID > 0 AND ItemID IS NOT NULL AND StationID IN (102, 104) -- Ordered, Partially Billed ) TD JOIN TransHeader TH ON TH.ID = TD.TransHeaderID LEFT JOIN Inventory I ON (I.PartID = TD.PartID AND I.WarehouseID = TD.WarehouseID) LEFT JOIN Part P ON P.ID = TD.PartID LEFT JOIN ( SELECT PartID, UnitID, PartInventoryConversion.ConversionFormula, CASE WHEN PartInventoryConversion.ConversionFormula IS NULL THEN 1.00 WHEN PATINDEX( '%/%', PartInventoryConversion.ConversionFormula ) > 0 THEN CAST(SUBSTRING(PartInventoryConversion.ConversionFormula, 0, PATINDEX( '%/%', PartInventoryConversion.ConversionFormula )) AS FLOAT) ELSE CAST(CAST(PartInventoryConversion.ConversionFormula AS VARCHAR(100)) AS FLOAT) END FxUpper, CASE WHEN PartInventoryConversion.ConversionFormula IS NULL THEN 1.00 WHEN PATINDEX( '%/%', PartInventoryConversion.ConversionFormula ) > 0 THEN CAST(SUBSTRING(PartInventoryConversion.ConversionFormula, PATINDEX( '%/%', PartInventoryConversion.ConversionFormula )+1, 999) AS FLOAT) ELSE 1.00 END FxLower FROM PartInventoryConversion ) IC ON IC.PartID = TD.PartID AND IC.UnitID = TD.UnitID WHERE TH.TransactionType = 7 -- PO AND TH.StatusID = 27 -- Ordered AND I.ID IS NOT NULL GROUP BY I.ID ; UPDATE @T SET ActualOnOrder = ISNULL( CR.Amount, 0 ) FROM @T T LEFT JOIN @ActualOnOrder CR ON CR.InventoryID = T.InventoryID ; -- Delete all inventory records that do not require a change from the temp table DELETE FROM @T WHERE CurrentOnOrder = ActualOnOrder ; IF @CommitChanges = 1 BEGIN -- Compute the IDs for the new records DECLARE @LastJournalID INT; SET @LastJournalID = (SELECT MAX(ID) FROM Journal); UPDATE T SET New_JournalID = RowNum + @LastJournalID FROM ( SELECT x.New_JournalID, ROW_NUMBER() OVER(ORDER BY x.InventoryID) AS RowNum FROM @T x ) AS T ; -- Insert Journal entry INSERT INTO Journal (ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive, JournalActivityType, JournalActivityText, Description, Notes, StartDateTime, EndDateTime, CompletedDateTime, IsSummary, IsDetail, SummaryAmount, DetailAmount, StartGLGroupID, EndGLGroupID, IsVoided, QueryStartDateTime, QueryEndDateTime, ReminderPrompt, PartID, PartClassTypeID, ActivityType, ActivityTypeText, IsBillable, UseActualTime, BillingType, TotalBilledTime, DivisionID, HasCalendarLinks) SELECT New_JournalID, -1, 20530, @ModifiedByUser, @ModifiedByComputer, @ModifiedDate, 0, 0, 1, 17, 'Part Usage', @Description, @Notes, @EntryDate, @EntryDate, @EntryDate, 1, 1, 0, 0, NULL, NULL, 0, @EntryDate, @EntryDate, 0, PartID, 12014, 43, 'Inventory Edited', 0, 0, 0, 0, DivisionID, 0 FROM @T ; -- Insert InventoryLog entry INSERT INTO InventoryLog (ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive, QuantityBilled, QuantityReceivedOnly, QuantityReserved, QuantityOnOrder, UnitCost, Cost, PartID, PartClassTypeID, InventoryID, FromWarehouseID, FromDivisionID, SortIndex, UnitID) SELECT New_JournalID, -1, 20530, @ModifiedByUser, @ModifiedByComputer, @ModifiedDate, 0, 0, 1, 0, 0, 0, ActualOnOrder - CurrentOnOrder, 0, 0, PartID, 12014, InventoryID, WarehouseID, DivisionID, 0, 1 FROM @T T ; END ELSE SELECT ItemName AS PartName, T.InventoryID, PartID, DivisionID, WarehouseID, CurrentOnOrder, ActualOnOrder FROM @T T LEFT JOIN Part P ON P.ID = T.PartID
Version Information
- Revised : 11/2014
- Version : Control 5.1