Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
control_sql_-_check_inventory_received_levels_on_pos [2022/07/07 10:39]
shendrix [Risk of Data Corruption if Run Improperly] 		control_sql_-_check_inventory_received_levels_on_pos [2022/07/07 11:20] (current)
shendrix [SQL Query to Reduce Received without changing Billed Qty]
Line 32: Line 32:
 ====== SQL Queries ====== ====== 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.
  
- 
-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 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.   - 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. 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 All users should be out of the system when running this query
  
-**Restart Chapi and the SSLIP** otherwise duplicate Journal records will be created. +**Restart Chapi and the SSLIP**  otherwise duplicate Journal records will be created.
- +
- +
- +
-<code>+
  
 +<code sql>
 DECLARE @CommitChanges BIT; DECLARE @CommitChanges BIT;
 SET @CommitChanges = 0; SET @CommitChanges = 0;
Line 186: Line 178:
 ELSE ELSE
 SELECT InventoryID, PartID, ItemName, DivisionID, WarehouseID, CurrentReceivedOnly, ActualReceivedOnly FROM @T SELECT InventoryID, PartID, ItemName, DivisionID, WarehouseID, CurrentReceivedOnly, ActualReceivedOnly FROM @T
- 
 </code> </code>
 +
  
 ===== SQL Query to Reduce Received without changing Billed Qty ===== ===== SQL Query to Reduce Received without changing Billed Qty =====
- 
- 
  
 <code sql> <code sql>
 +
 DECLARE @CommitChanges BIT; DECLARE @CommitChanges BIT;
 SET @CommitChanges = 0; SET @CommitChanges = 0;
Line 209: Line 200:
 SET @ModifiedByComputer = '(cyrious rec adj)'; SET @ModifiedByComputer = '(cyrious rec adj)';
 DECLARE @T TABLE(InventoryID INT, DECLARE @T TABLE(InventoryID INT,
-                 PartID INT, +               PartID INT, 
-                 DivisionID INT, +      ItemName VARCHAR(100), 
-                 WarehouseID INT, +               DivisionID INT, 
-                 CurrentReceivedOnly FLOAT, +               WarehouseID INT, 
-                 ActualReceivedOnly FLOAT, +               CurrentReceivedOnly FLOAT, 
-                 New_JournalID INT )+               ActualReceivedOnly FLOAT, 
 +               New_JournalID INT )
 ; ;
 -- Insert all of the inventory items into the temp table -- Insert all of the inventory items into the temp table
 INSERT INTO @T INSERT INTO @T
-(InventoryID, PartID, DivisionID, WarehouseID) +(InventoryID, PartID, ItemName, DivisionID, WarehouseID) 
-SELECT I.ID, I.PartID, I.DivisionID, I.WarehouseID+SELECT I.ID, I.PartID, I.ItemName, I.DivisionID, I.WarehouseID
 FROM   Inventory I FROM   Inventory I
-       LEFT JOIN Part P ON P.ID = I.PartID+     LEFT JOIN Part P ON P.ID = I.PartID
 WHERE  P.PartType = 0 AND I.IsGroup = 0 AND P.TrackInventory = 1 WHERE  P.PartType = 0 AND I.IsGroup = 0 AND P.TrackInventory = 1
 ; ;
Line 228: Line 220:
 SET  CurrentReceivedOnly = ISNULL( IL.QuantityReceivedOnly, 0 ) SET  CurrentReceivedOnly = ISNULL( IL.QuantityReceivedOnly, 0 )
 FROM @T T FROM @T T
-     LEFT JOIN ( SELECT   InventoryID +   LEFT JOIN ( SELECT   InventoryID 
-                            , ROUND( SUM( ISNULL( QuantityReceivedOnly, 0 ) ), 4 ) AS QuantityReceivedOnly +                          , ROUND( SUM( ISNULL( QuantityReceivedOnly, 0 ) ), 4 ) AS QuantityReceivedOnly 
-                 FROM     InventoryLog +               FROM     InventoryLog 
-                 WHERE    InventoryID IS NOT NULL +               WHERE    InventoryID IS NOT NULL 
-                 GROUP BY InventoryID ) IL ON IL.InventoryID = T.InventoryID+               GROUP BY InventoryID ) IL ON IL.InventoryID = T.InventoryID
 ; ;
 -- Compute the actual on order quantity and set the value -- Compute the actual on order quantity and set the value
Line 238: Line 230:
 INSERT INTO @ActualReceivedOnly INSERT INTO @ActualReceivedOnly
 SELECT I.ID InventoryID, SELECT I.ID InventoryID,
-       SUM( CASE WHEN TD.UnitID = P.UnitID THEN TD.POQuantityReceivedOnly +     SUM( CASE WHEN TD.UnitID = P.UnitID THEN TD.POQuantityReceivedOnly 
-                 ELSE ( TD.POQuantityReceivedOnly * ISNULL(IC.FxUpper / IC.FxLower, 1 ) ) +               ELSE ( TD.POQuantityReceivedOnly * ISNULL(IC.FxUpper / IC.FxLower, 1 ) ) 
-            END ) POQuantityReceivedOnly+          END ) POQuantityReceivedOnly
 FROM   ( SELECT TransHeaderID, FROM   ( SELECT TransHeaderID,
-                ( CASE WHEN ItemClassTypeID = 12076 THEN (SELECT TOP 1 PartID FROM CatalogItem WHERE ID = ItemID) +              ( CASE WHEN ItemClassTypeID = 12076 THEN (SELECT TOP 1 PartID FROM CatalogItem WHERE ID = ItemID) 
-                       ELSE ItemID +                     ELSE ItemID 
-                  END ) PartID, +                END ) PartID, 
-                UnitID, +              UnitID, 
-                Quantity, +              Quantity, 
-                BillQuantity, +              BillQuantity, 
-                ( ISNULL( Quantity, 0 ) - ISNULL( BillQuantity, 0 ) ) AS POQuantityReceivedOnly, +              ( ISNULL( Quantity, 0 ) - ISNULL( BillQuantity, 0 ) ) AS POQuantityReceivedOnly, 
-                WarehouseID +              WarehouseID 
-         FROM VendorTransDetail +       FROM VendorTransDetail 
-         WHERE ID > 0 +       WHERE ID > 0 
-               AND ItemID IS NOT NULL +             AND ItemID IS NOT NULL 
-               AND StationID IN (108, 109) -- Received, Partially Received +             AND StationID IN (108, 109) -- Received, Partially Received 
-       ) TD +     ) TD 
-       JOIN TransHeader TH ON TH.ID = TD.TransHeaderID +     JOIN TransHeader TH ON TH.ID = TD.TransHeaderID 
-       LEFT JOIN Inventory I ON (I.PartID = TD.PartID AND I.WarehouseID = TD.WarehouseID) +     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 Part P ON P.ID = TD.PartID 
-       LEFT JOIN ( SELECT PartID, +     LEFT JOIN ( SELECT PartID, 
-                          UnitID, +                        UnitID, 
-                          PartInventoryConversion.ConversionFormula, +                        PartInventoryConversion.ConversionFormula, 
-                          CASE +                        CASE 
-                            WHEN PartInventoryConversion.ConversionFormula IS NULL THEN 1.00 +                          WHEN PartInventoryConversion.ConversionFormula IS NULL THEN 1.00 
-                            WHEN PATINDEX( '%/%', PartInventoryConversion.ConversionFormula ) > 0 THEN CAST(SUBSTRING(PartInventoryConversion.ConversionFormula, 0, PATINDEX( '%/%', PartInventoryConversion.ConversionFormula )) AS FLOAT) +                          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) +                          ELSE CAST(CAST(PartInventoryConversion.ConversionFormula AS VARCHAR(100)) AS FLOAT) 
-                          END FxUpper, +                        END FxUpper, 
-                          CASE +                        CASE 
-                            WHEN PartInventoryConversion.ConversionFormula IS NULL THEN 1.00 +                          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) +                          WHEN PATINDEX( '%/%', PartInventoryConversion.ConversionFormula ) > 0 THEN CAST(SUBSTRING(PartInventoryConversion.ConversionFormula, PATINDEX( '%/%', PartInventoryConversion.ConversionFormula )+1, 999) AS FLOAT) 
-                            ELSE 1.00 +                          ELSE 1.00 
-                          END FxLower +                        END FxLower 
-                   FROM PartInventoryConversion ) IC ON IC.PartID = TD.PartID AND IC.UnitID = TD.UnitID+                 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. 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 GROUP BY I.ID
Line 278: Line 270:
 SET ActualReceivedOnly = ISNULL( CR.Amount, 0 ) SET ActualReceivedOnly = ISNULL( CR.Amount, 0 )
 FROM @T T FROM @T T
-     LEFT JOIN @ActualReceivedOnly CR ON CR.InventoryID = T.InventoryID+   LEFT JOIN @ActualReceivedOnly CR ON CR.InventoryID = T.InventoryID
 ; ;
 -- Delete all inventory records that do not require a change from the temp table -- Delete all inventory records that do not require a change from the temp table
Line 286: Line 278:
 IF @CommitChanges = 1 IF @CommitChanges = 1
 BEGIN BEGIN
-    -- Compute the IDs for the new records +  -- Compute the IDs for the new records 
-    DECLARE @LastJournalID INT; +  DECLARE @LastJournalID INT; 
-    SET @LastJournalID = (SELECT MAX(ID) FROM Journal); +  SET @LastJournalID = (SELECT MAX(ID) FROM Journal); 
-    UPDATE T +  UPDATE T 
-    SET New_JournalID = RowNum + @LastJournalID +  SET New_JournalID = RowNum + @LastJournalID 
-    FROM +  FROM 
-    ( +  ( 
-        SELECT  x.New_JournalID, +      SELECT  x.New_JournalID, 
-                ROW_NUMBER() OVER(ORDER BY x.InventoryID) AS RowNum +              ROW_NUMBER() OVER(ORDER BY x.InventoryID) AS RowNum 
-        FROM    @T x +      FROM    @T x 
-    ) AS T +  ) AS T 
-    ; +  ; 
-    -- Insert Journal entry +  -- Insert Journal entry 
-    INSERT INTO Journal +  INSERT INTO Journal 
-    (ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive, +  (ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive, 
-     JournalActivityType, JournalActivityText, Description, Notes, StartDateTime, EndDateTime, CompletedDateTime, +   JournalActivityType, JournalActivityText, Description, Notes, StartDateTime, EndDateTime, CompletedDateTime, 
-     IsSummary, IsDetail, SummaryAmount, DetailAmount, StartGLGroupID, EndGLGroupID, IsVoided, +   IsSummary, IsDetail, SummaryAmount, DetailAmount, StartGLGroupID, EndGLGroupID, IsVoided, 
-     QueryStartDateTime, QueryEndDateTime, ReminderPrompt, PartID, PartClassTypeID, +   QueryStartDateTime, QueryEndDateTime, ReminderPrompt, PartID, PartClassTypeID, 
-     ActivityType, ActivityTypeText, IsBillable, UseActualTime, BillingType, TotalBilledTime, DivisionID, HasCalendarLinks) +   ActivityType, ActivityTypeText, IsBillable, UseActualTime, BillingType, TotalBilledTime, DivisionID, HasCalendarLinks) 
-    SELECT New_JournalID, -1, 20530, @ModifiedByUser, @ModifiedByComputer, @ModifiedDate, 0, 0, 1, +  SELECT New_JournalID, -1, 20530, @ModifiedByUser, @ModifiedByComputer, @ModifiedDate, 0, 0, 1, 
-           17, 'Part Usage', @Description, @Notes, @EntryDate, @EntryDate, @EntryDate, +         17, 'Part Usage', @Description, @Notes, @EntryDate, @EntryDate, @EntryDate, 
-           1, 1, 0, 0, NULL, NULL, 0, +         1, 1, 0, 0, NULL, NULL, 0, 
-           @EntryDate, @EntryDate, 0, PartID, 12014, +         @EntryDate, @EntryDate, 0, PartID, 12014, 
-           43, 'Inventory Edited', 0, 0, 0, 0, DivisionID, 0 +         43, 'Inventory Edited', 0, 0, 0, 0, DivisionID, 0 
-    FROM @T +  FROM @T 
-    ; +  ; 
-    -- Insert InventoryLog entry +  -- Insert InventoryLog entry 
-    INSERT INTO InventoryLog +  INSERT INTO InventoryLog 
-    (ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive, +  (ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive, 
-     QuantityBilled, QuantityReceivedOnly, QuantityReserved, QuantityOnOrder, +   QuantityBilled, QuantityReceivedOnly, QuantityReserved, QuantityOnOrder, 
-     UnitCost, Cost, PartID, PartClassTypeID, +   UnitCost, Cost, PartID, PartClassTypeID, 
-     InventoryID, FromWarehouseID, FromDivisionID, SortIndex, UnitID) +   InventoryID, FromWarehouseID, FromDivisionID, SortIndex, UnitID) 
-    SELECT New_JournalID, -1, 20530, @ModifiedByUser, @ModifiedByComputer, @ModifiedDate, 0, 0, 1, +  SELECT New_JournalID, -1, 20530, @ModifiedByUser, @ModifiedByComputer, @ModifiedDate, 0, 0, 1, 
-           0, ActualReceivedOnly - CurrentReceivedOnly, 0, 0, +         0, ActualReceivedOnly - CurrentReceivedOnly, 0, 0, 
-           0, 0, PartID, 12014, +         0, 0, PartID, 12014, 
-           InventoryID, WarehouseID, DivisionID, 0, 1 +         InventoryID, WarehouseID, DivisionID, 0, 1 
-    FROM @T T +  FROM @T T 
-    ;+  ;
 END END
 ELSE ELSE
-    SELECT InventoryID, PartID, DivisionID, WarehouseID, CurrentReceivedOnly, ActualReceivedOnly FROM @T+  SELECT InventoryID, PartID, ItemName,  DivisionID, WarehouseID, CurrentReceivedOnly, ActualReceivedOnly FROM @T
 </code> </code>
- 
- 
- 
 ====== Version Information ====== ====== Version Information ======
   * Revised : 07/2018   * Revised : 07/2018