Differences

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

Link to this comparison view

Next revision 		Previous revision
control_sql_-_check_inventory_received_levels_on_pos [2019/01/27 11:28]
127.0.0.1 external edit 		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 28: Line 28:
  
 **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**. **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 ====== ====== 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.
- +
- +
- +
-===== SQL Query to Reduce Received, Increase Billed Qty ===== +
- +
  
 <code sql> <code sql>
Line 72: Line 60:
 DECLARE @T TABLE(InventoryID INT, DECLARE @T TABLE(InventoryID INT,
 PartID INT, PartID INT,
 +ItemName VARCHAR(100),
 DivisionID INT, DivisionID INT,
 WarehouseID INT, WarehouseID INT,
Line 80: Line 69:
 -- 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
Line 113: Line 102:
 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
Line 125: Line 114:
 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
Line 188: Line 177:
 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>
- 
  
  
 ===== 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 213: 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 232: 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 242: 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 282: 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 290: 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