Explanation of SQL
The following query will locate and repair any discrepancies between the Received amount in inventory and the total Received on P.O.s. This only works with PO's, if you are using Receiving Documents in conjunction with PO's then you will need a different query.
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 Queries
There are two SQL queries listed below, it is very important that you understand the difference between these two queries and only apply the one that fits the scenario that you are encountering.
- The first query will post any adjustment necessary to Received while also posting an opposite adjustment to the Billed Qty on the part.
- The second query will post any adjustment necessary to Received while not posting any adjustment to the Billed Qty on the part.
If you do not understand when you should use one of the queries above vs. the other please consult assistance prior to running these the database.
All users should be out of the system when running this query
Restart Chapi and the SSLIP otherwise duplicate Journal records will be created.
DECLARE @CommitChanges BIT; SET @CommitChanges = 0; DECLARE @EntryDate DateTime; SET @EntryDate = GetDate(); DECLARE @Description VARCHAR(255); SET @Description = 'Inventory Received Adjustment by Cyrious'; DECLARE @Notes VARCHAR(MAX); SET @Notes = 'Inventory Received 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, ItemName VARCHAR(100), DivisionID INT, WarehouseID INT, CurrentReceivedOnly FLOAT, ActualReceivedOnly FLOAT, New_JournalID INT ) ; -- Insert all of the inventory items into the temp table INSERT INTO @T (InventoryID, PartID, ItemName, DivisionID, WarehouseID) SELECT I.ID, I.PartID,I.ItemName, I.DivisionID, I.WarehouseID FROM Inventory I LEFT JOIN Part P ON P.ID = I.PartID WHERE P.PartType = 0 AND I.IsGroup = 0 AND P.TrackInventory = 1 ; -- Set the current inventory items UPDATE @T SET CurrentReceivedOnly = ISNULL( IL.QuantityReceivedOnly, 0 ) FROM @T T LEFT JOIN ( SELECT InventoryID , ROUND( SUM( ISNULL( QuantityReceivedOnly, 0 ) ), 4 ) AS QuantityReceivedOnly 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 @ActualReceivedOnly TABLE (InventoryID INT PRIMARY KEY, Amount FLOAT); INSERT INTO @ActualReceivedOnly SELECT I.ID InventoryID, SUM( CASE WHEN TD.UnitID = P.UnitID THEN TD.POQuantityReceivedOnly ELSE ( TD.POQuantityReceivedOnly * ISNULL(IC.FxUpper / IC.FxLower, 1 ) ) END ) POQuantityReceivedOnly FROM ( SELECT TransHeaderID, ( CASE WHEN ItemClassTypeID = 12076 THEN (SELECT TOP 1 PartID FROM CatalogItem WHERE ID = ItemID) ELSE ItemID END ) PartID, UnitID, Quantity, BillQuantity, RDQuantity AS POQuantityReceivedOnly, WarehouseID FROM VendorTransDetail WHERE ID> 0 AND ItemID IS NOT NULL AND StationID IN (108, 109) -- Received, Partially Received ) 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 AND I.ID IS NOT NULL -- PO, we don't filter on status since we're filtering on line item statuses. GROUP BY I.ID ; UPDATE @T SET ActualReceivedOnly = ISNULL( CR.Amount, 0 ) FROM @T T LEFT JOIN @ActualReceivedOnly CR ON CR.InventoryID = T.InventoryID ; -- Delete all inventory records that do not require a change from the temp table DELETE FROM @T WHERE CurrentReceivedOnly = ActualReceivedOnly ; 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, -(ActualReceivedOnly - CurrentReceivedOnly), ActualReceivedOnly - CurrentReceivedOnly, 0, 0, 0, 0, PartID, 12014, InventoryID, WarehouseID, DivisionID, 0, 1 FROM @T T ; END ELSE SELECT InventoryID, PartID, ItemName, DivisionID, WarehouseID, CurrentReceivedOnly, ActualReceivedOnly FROM @T
SQL Query to Reduce Received without changing Billed Qty
DECLARE @CommitChanges BIT; SET @CommitChanges = 0; DECLARE @EntryDate DateTime; SET @EntryDate = GETDATE(); DECLARE @Description VARCHAR(255); SET @Description = 'Inventory Received Adj by Cyrious'; DECLARE @Notes VARCHAR(MAX); SET @Notes = 'Inventory Received Adj by Cyrious'; DECLARE @ModifiedDate DateTime; SET @ModifiedDate = getdate(); DECLARE @ModifiedByUser VARCHAR(100); SET @ModifiedByUser = '(cyrious rec adj)'; DECLARE @ModifiedByComputer VARCHAR(100); SET @ModifiedByComputer = '(cyrious rec adj)'; DECLARE @T TABLE(InventoryID INT, PartID INT, ItemName VARCHAR(100), DivisionID INT, WarehouseID INT, CurrentReceivedOnly FLOAT, ActualReceivedOnly FLOAT, New_JournalID INT ) ; -- Insert all of the inventory items into the temp table INSERT INTO @T (InventoryID, PartID, ItemName, DivisionID, WarehouseID) SELECT I.ID, I.PartID, I.ItemName, I.DivisionID, I.WarehouseID FROM Inventory I LEFT JOIN Part P ON P.ID = I.PartID WHERE P.PartType = 0 AND I.IsGroup = 0 AND P.TrackInventory = 1 ; -- Set the current inventory items UPDATE @T SET CurrentReceivedOnly = ISNULL( IL.QuantityReceivedOnly, 0 ) FROM @T T LEFT JOIN ( SELECT InventoryID , ROUND( SUM( ISNULL( QuantityReceivedOnly, 0 ) ), 4 ) AS QuantityReceivedOnly 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 @ActualReceivedOnly TABLE (InventoryID INT PRIMARY KEY, Amount FLOAT); INSERT INTO @ActualReceivedOnly SELECT I.ID InventoryID, SUM( CASE WHEN TD.UnitID = P.UnitID THEN TD.POQuantityReceivedOnly ELSE ( TD.POQuantityReceivedOnly * ISNULL(IC.FxUpper / IC.FxLower, 1 ) ) END ) POQuantityReceivedOnly 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 POQuantityReceivedOnly, WarehouseID FROM VendorTransDetail WHERE ID > 0 AND ItemID IS NOT NULL AND StationID IN (108, 109) -- Received, Partially Received ) 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 AND I.ID IS NOT NULL -- PO, we don't filter on status since we're filtering on line item statuses. GROUP BY I.ID ; UPDATE @T SET ActualReceivedOnly = ISNULL( CR.Amount, 0 ) FROM @T T LEFT JOIN @ActualReceivedOnly CR ON CR.InventoryID = T.InventoryID ; -- Delete all inventory records that do not require a change from the temp table DELETE FROM @T WHERE CurrentReceivedOnly = ActualReceivedOnly ; 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, ActualReceivedOnly - CurrentReceivedOnly, 0, 0, 0, 0, PartID, 12014, InventoryID, WarehouseID, DivisionID, 0, 1 FROM @T T ; END ELSE SELECT InventoryID, PartID, ItemName, DivisionID, WarehouseID, CurrentReceivedOnly, ActualReceivedOnly FROM @T
Version Information
- Revised : 07/2018
- Version : Control 6.1