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