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 185: Line 184:
  
 <code sql> <code sql>
 +
 DECLARE @CommitChanges BIT; DECLARE @CommitChanges BIT;
 SET @CommitChanges = 0; SET @CommitChanges = 0;
Line 200: 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, 
-  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
Line 213: Line 213:
 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
 ; ;
Line 220: 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 230: 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 270: 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 278: 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, 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