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 11:14]
orichards [SQL Query to Reduce Received without changing Billed Qty] 		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 178: 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>
  
Line 184: Line 183:
 ===== SQL Query to Reduce Received without changing Billed Qty ===== ===== SQL Query to Reduce Received without changing Billed Qty =====
  
-''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, '' +<code sql> 
-<code> + 
-''               PartID INT, +DECLARE @CommitChanges BIT; 
-     ItemName VARCHAR(100),+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, 
 +      ItemName VARCHAR(100),
                DivisionID INT,                DivisionID INT,
                WarehouseID INT,                WarehouseID INT,
Line 193: Line 207:
                ActualReceivedOnly FLOAT,                ActualReceivedOnly FLOAT,
                New_JournalID INT )                New_JournalID INT )
- +; 
-'' +-- Insert all of the inventory items into the temp table 
- +INSERT INTO @T 
-</code> +(InventoryID, PartID, ItemName, DivisionID, WarehouseID) 
-'' '' +SELECT I.ID, I.PartID, I.ItemName, I.DivisionID, I.WarehouseID 
- +FROM   Inventory I 
-''; – Insert all of the inventory items into the temp table INSERT INTO @T (InventoryID, PartID, ItemName, DivisionID, WarehouseID) SELECT I.ID, I.PartID, I.ItemName, I.DivisionID, I.WarehouseID FROM Inventory I '' +     LEFT JOIN Part P ON P.ID = I.PartID 
-<code> +WHERE  P.PartType = 0 AND I.IsGroup = 0 AND P.TrackInventory = 1 
-''     LEFT JOIN Part P ON P.ID = I.PartID +; 
- +-- Set the current inventory items 
-'' +UPDATE @T 
- +SET  CurrentReceivedOnly = ISNULL( IL.QuantityReceivedOnly, 0 ) 
-</code> +FROM @T T 
-'' '' +   LEFT JOIN ( SELECT   InventoryID
- +
-''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 '' +
-<code> +
-''   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 
- +DECLARE @ActualReceivedOnly TABLE (InventoryID INT PRIMARY KEY, Amount FLOAT); 
-</code> +INSERT INTO @ActualReceivedOnly 
-'' '' +SELECT I.ID InventoryID, 
- +     SUM( CASE WHEN TD.UnitID = P.UnitID THEN TD.POQuantityReceivedOnly
-''; – 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, '' +
-<code> +
-''     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, 
-'' +              ( CASE WHEN ItemClassTypeID = 12076 THEN (SELECT TOP 1 PartID FROM CatalogItem WHERE ID = ItemID)
- +
-</code> +
-'' '' +
- +
-''FROM ( SELECT TransHeaderID, '' +
-<code> +
-''              ( CASE WHEN ItemClassTypeID = 12076 THEN (SELECT TOP 1 PartID FROM CatalogItem WHERE ID = ItemID)+
                      ELSE ItemID                      ELSE ItemID
                 END ) PartID,                 END ) PartID,
Line 243: Line 243:
               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 255: Line 255:
                         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. 
-'' +GROUP BY I.ID 
- +; 
-</code> +UPDATE @T 
-'' '' +SET ActualReceivedOnly = ISNULL( CR.Amount, 0 ) 
- +FROM @T T 
-''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 
-<code> +; 
-''   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 
- +; 
-</code> +IF @CommitChanges = 1 
-'' '' +BEGIN 
- +  -- Compute the IDs for the new records 
-''; – Delete all inventory records that do not require a change from the temp table DELETE FROM @T WHERE CurrentReceivedOnly = ActualReceivedOnly ; IF @CommitChanges = 1 BEGIN '' +  DECLARE @LastJournalID INT; 
- +  SET @LastJournalID = (SELECT MAX(ID) FROM Journal);
-   - ''- Compute the IDs for the new records'' +
-''DECLARE @LastJournalID INT; '' +
- +
-<code> +
-''  SET @LastJournalID = (SELECT MAX(ID) FROM Journal);+
   UPDATE T   UPDATE T
   SET New_JournalID = RowNum + @LastJournalID   SET New_JournalID = RowNum + @LastJournalID
Line 321: Line 316:
   FROM @T T   FROM @T T
   ;   ;
- +END 
-'' +ELSE 
 +  SELECT InventoryID, PartID, ItemName,  DivisionID, WarehouseID, CurrentReceivedOnly, ActualReceivedOnly FROM @T
 </code> </code>
- 
-'' '' 
- 
-''END ELSE '' 
- 
-<code> 
-''  SELECT InventoryID, PartID, ItemName,  DivisionID, WarehouseID, CurrentReceivedOnly, ActualReceivedOnly FROM @T 
- 
-'' 
- 
-</code> 
- 
-'' '' 
- 
- 
 ====== Version Information ====== ====== Version Information ======
   * Revised : 07/2018   * Revised : 07/2018