Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_check_inventory_on-order_levels_v_ordered_pos [2019/01/27 11:28] (current)
Line 1: Line 1:
 +====== 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 [[http://​control.cyriouswiki.com/​inventory_out_of_balance_repair|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 ======
 +
 +
 +
 +<code sql>
 +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
 +</​code>​
 +
 +
 +
 +====== Version Information ======
 +  * Revised : 11/2014
 +  * Version : Control 5.1
 +
 +
 +
 +====== Related SQLs ======
 +