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:16]
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 186: Line 185:
 <code sql> <code sql>
  
-DECLARE @CommitChanges BIT;\\ +DECLARE @CommitChanges BIT; 
-SET @CommitChanges = 0;\\ +SET @CommitChanges = 0; 
-DECLARE @EntryDate DateTime;\\ +DECLARE @EntryDate DateTime; 
-SET @EntryDate = GETDATE();\\ +SET @EntryDate = GETDATE(); 
-DECLARE @Description VARCHAR(255);\\ +DECLARE @Description VARCHAR(255); 
-SET @Description = 'Inventory Received Adj by Cyrious';\\ +SET @Description = 'Inventory Received Adj by Cyrious'; 
-DECLARE @Notes VARCHAR(MAX);\\ +DECLARE @Notes VARCHAR(MAX); 
-SET @Notes = 'Inventory Received Adj by Cyrious';\\ +SET @Notes = 'Inventory Received Adj by Cyrious'; 
-DECLARE @ModifiedDate DateTime;\\ +DECLARE @ModifiedDate DateTime; 
-SET @ModifiedDate = getdate();\\ +SET @ModifiedDate = getdate(); 
-DECLARE @ModifiedByUser VARCHAR(100);\\ +DECLARE @ModifiedByUser VARCHAR(100); 
-SET @ModifiedByUser = '(cyrious rec adj)';\\ +SET @ModifiedByUser = '(cyrious rec adj)'; 
-DECLARE @ModifiedByComputer VARCHAR(100);\\ +DECLARE @ModifiedByComputer VARCHAR(100); 
-SET @ModifiedByComputer = '(cyrious rec adj)';\\ +SET @ModifiedByComputer = '(cyrious rec adj)'; 
-DECLARE @T TABLE(InventoryID INT,\\ +DECLARE @T TABLE(InventoryID INT, 
-               PartID INT,\\ +               PartID INT, 
-      ItemName VARCHAR(100),\\ +      ItemName VARCHAR(100), 
-               DivisionID INT,\\ +               DivisionID INT, 
-               WarehouseID INT,\\ +               WarehouseID INT, 
-               CurrentReceivedOnly FLOAT,\\ +               CurrentReceivedOnly FLOAT, 
-               ActualReceivedOnly FLOAT,\\ +               ActualReceivedOnly FLOAT, 
-               New_JournalID INT )\\ +               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, ItemName, DivisionID, WarehouseID)\\ +(InventoryID, PartID, ItemName, DivisionID, WarehouseID) 
-SELECT I.ID, I.PartID, I.ItemName, 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 
-;\\ +; 
--- Set the current inventory items\\ +-- Set the current inventory items 
-UPDATE @T\\ +UPDATE @T 
-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 
-DECLARE @ActualReceivedOnly TABLE (InventoryID INT PRIMARY KEY, Amount FLOAT);\\ +DECLARE @ActualReceivedOnly TABLE (InventoryID INT PRIMARY KEY, Amount FLOAT); 
-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 
-;\\ +; 
-UPDATE @T\\ +UPDATE @T 
-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 
-DELETE FROM @T\\ +DELETE FROM @T 
-WHERE CurrentReceivedOnly = ActualReceivedOnly\\ +WHERE CurrentReceivedOnly = ActualReceivedOnly 
-;\\ +; 
-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, ItemName,  DivisionID, WarehouseID, CurrentReceivedOnly, ActualReceivedOnly FROM @T   SELECT InventoryID, PartID, ItemName,  DivisionID, WarehouseID, CurrentReceivedOnly, ActualReceivedOnly FROM @T
- +</code>
 ====== Version Information ====== ====== Version Information ======
   * Revised : 07/2018   * Revised : 07/2018