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.

1. Execute the Inventory Out of Balance Repair Query.
2. Restart Chapi and the SSLIP.

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.

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.

1. The first query will post any adjustment necessary to Received while also posting an opposite adjustment to the Billed Qty on the part.
2. 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

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

• Revised : 07/2018
• Version : Control 6.1