Differences

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

Link to this comparison view

control_sql_-_check_inventory_received_levels_on_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 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 [[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 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.
 +
 +
 +
 +===== SQL Query to Reduce Received, Increase Billed Qty =====
 +
 +
 +
 +<code sql>
 +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,
 +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, 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 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, DivisionID, WarehouseID,​ CurrentReceivedOnly,​ ActualReceivedOnly FROM @T
 +</​code>​
 +
 +
 +
 +===== SQL Query to Reduce Received without changing Billed Qty =====
 +
 +
 +
 +<code sql>
 +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,
 +                 ​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, 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 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, DivisionID, WarehouseID,​ CurrentReceivedOnly,​ ActualReceivedOnly FROM @T
 +</​code>​
 +
 +
 +
 +====== Version Information ======
 +  * Revised : 07/2018
 +  * Version : Control 6.1
 +
 +
 +
 +====== Related SQLs ======
 +