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.

  1. 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

Related SQLs

You could leave a comment if you were logged in.