Recent Changes

Sunday, November 12

Tuesday, October 31

  1. page Control SQL - Adjust Reserved Inventory Levels to Match Orders edited ... The tool will execute and check for any discrepancies. If any are found then adjusting Invento…
    ...
    The tool will execute and check for any discrepancies. If any are found then adjusting Inventory Activities will be posted to the InventoryLog table to correct any issues.
    After the tool has been successfully run, it is recommended that you restart your server.
    SQL
    The query below will check the reserved quantity for you at an individual order and part instance level, it will not make any changes. This query is useful if you want to have a detailed breakdown on what discrepancies may be present and/or what makes up the total for reserved for a given part.
    DECLARE @CurrentReserved TABLE(TransPartID INT PRIMARY KEY CLUSTERED,
    TransHeaderID INT,
    TransDetailID INT,
    PartID INT,
    InventoryID INT,
    WarehouseID INT,
    DivisionID INT,
    QuantityReserved FLOAT)
    INSERT INTO @CurrentReserved
    SELECT TP.ID,
    TP.TransHeaderID,
    TP.TransDetailID,
    TP.PartID,
    TP.InventoryID,
    TP.WarehouseID,
    W.DivisionID,
    (TP.EstimatedValue - TP.ActualValue) AS NewResQty
    FROM TransPart TP WITH(NOLOCK)
    LEFT JOIN Part P WITH(NOLOCK) ON P.ID = TP.PartID
    LEFT JOIN TransHeader TH WITH(NOLOCK) on TH.ID = TP.TransHeaderID
    LEFT JOIN Warehouse W WITH(NOLOCK) ON W.ID = TP.WarehouseID
    WHERE TP.ID > 0
    AND ISNULL(P.TrackInventory, 0) <> 0
    AND ISNULL(TH.StatusID, -1) IN (0,1,2)
    AND ((TP.EstimatedValue - TP.ActualValue) >= 0)
    ;
    DECLARE @ActualReserved TABLE(PostDate DATETIME,
    TransPartID INT,
    TransHeaderID INT,
    TransDetailID INT,
    PartID INT,
    InventoryID INT,
    WarehouseID INT,
    DivisionID INT,
    ActualReserved FLOAT)
    ;
    INSERT INTO @ActualReserved
    SELECT J.StartDateTime,
    ISNULL(IL.TransPartID, -IL.PartID) TransPartID,
    J.TransactionID TransHeaderID,
    IL.TransDetailID, IL.PartID, IL.InventoryID,
    IL.FromWarehouseID WarehouseID,
    IL.FromDivisionID DivisionID,
    IL.QuantityReserved
    FROM InventoryLog IL WITH(NOLOCK) JOIN Journal J WITH(NOLOCK) ON J.ID = IL.ID
    WHERE IL.QuantityReserved <> 0 AND IL.ID > 0 AND IL.PartID IS NOT NULL
    ;
    DECLARE @Adjustments TABLE(TransPartID INT,
    TransHeaderID INT,
    TransDetailID INT,
    AccountID INT,
    PartID INT,
    InventoryID INT,
    WarehouseID INT,
    DivisionID INT,
    PostDate DATETIME,
    ActualReserved FLOAT,
    CurrentReserved FLOAT,
    AdjustedReserved FLOAT)
    ;
    INSERT INTO @Adjustments
    (TransPartID, TransHeaderID, TransDetailID, PartID, InventoryID, WarehouseID, DivisionID, ActualReserved, CurrentReserved, AdjustedReserved)
    SELECT ISNULL(CR.TransPartID, Inv.TransPartID) TransPartID,
    ISNULL(CR.TransHeaderID, Inv.TransHeaderID) TransHeaderID,
    ISNULL(CR.TransDetailID, Inv.TransDetailID) TransDetailID,
    ISNULL(CR.PartID, Inv.PartID) PartID,
    ISNULL(CR.InventoryID, Inv.InventoryID) InventoryID,
    ISNULL(CR.WarehouseID, Inv.WarehouseID) WarehouseID,
    ISNULL(CR.DivisionID, Inv.DivisionID) DivisionID,
    ISNULL(Inv.QuantityReserved, 0),
    ISNULL(CR.QuantityReserved, 0),
    ( ISNULL(CR.QuantityReserved, 0) - ISNULL(Inv.QuantityReserved, 0) ) QuantityReserved
    FROM @CurrentReserved CR
    FULL JOIN (SELECT A.TransPartID, A.TransHeaderID, A.TransDetailID, A.PartID,
    A.InventoryID, A.WarehouseID, A.DivisionID,
    ROUND( SUM(A.ActualReserved), 8 ) QuantityReserved
    FROM @ActualReserved A
    GROUP BY A.TransPartID, A.TransHeaderID, A.TransDetailID, A.PartID,
    A.InventoryID, A.WarehouseID, A.DivisionID) Inv
    ON (ISNULL(Inv.TransPartID, -1) = ISNULL(CR.TransPartID, -1))
    AND (ISNULL(Inv.TransHeaderID, -1) = ISNULL(CR.TransHeaderID, -1))
    AND (ISNULL(Inv.TransDetailID, -1) = ISNULL(CR.TransDetailID, -1))
    AND (ISNULL(Inv.PartID, -1) = ISNULL(CR.PartID, -1))
    AND (ISNULL(Inv.InventoryID, -1) = ISNULL(CR.InventoryID, -1))
    AND (ISNULL(Inv.WarehouseID, -1) = ISNULL(CR.WarehouseID, -1))
    AND (ISNULL(Inv.DivisionID, -1) = ISNULL(CR.DivisionID, -1))
    --WHERE ABS( ISNULL(CR.QuantityReserved, 0) - ISNULL(Inv.QuantityReserved, 0) ) > 0.0001
    WHERE ABS( ISNULL(CR.QuantityReserved, 0)) > 0 OR ABS(ISNULL(Inv.QuantityReserved, 0) ) > 0
    ;
    UPDATE A
    SET AccountID = TH.AccountID
    FROM @Adjustments A
    LEFT JOIN TransHeader TH ON TH.ID = A.TransHeaderID
    WHERE A.AccountID IS NULL
    ;
    UPDATE A
    SET PostDate = TH.VoidedDate
    FROM @Adjustments A
    LEFT JOIN TransHeader TH ON TH.ID = A.TransHeaderID
    WHERE PostDate IS NULL
    ;
    UPDATE A
    SET PostDate = TH.BuiltDate
    FROM @Adjustments A
    LEFT JOIN TransHeader TH ON TH.ID = A.TransHeaderID
    WHERE PostDate IS NULL
    ;
    UPDATE A
    SET PostDate = T.LastPostDate
    FROM @Adjustments A
    LEFT JOIN (SELECT TransPartID, MAX(PostDate) LastPostDate
    FROM @ActualReserved
    GROUP BY TransPartID) T ON T.TransPartID = A.TransPartID
    WHERE PostDate IS NULL
    ;
    UPDATE A
    SET PostDate = GETDATE()
    FROM @Adjustments A
    WHERE PostDate IS NULL
    ;
    SELECT
    P.ItemName as Part
    , D.DivisionName as [Division]
    , W.WarehouseName as [Warehouse]
    , OrderNumber as [Order No]
    , TD.GoodsItemCode as [Product]
    , TD.LineItemNumber as [Line Item No]
    , CAST(OrderCreatedDate as Date) as [Order Date]
    , StatusText as [Status]
    , A.CurrentReserved as [Current Reserved Qty]
    , A.ActualReserved as [Correct Reserved Qty]
    , ROUND(A.AdjustedReserved,2) as Discrepancy
    , A.TransPartID
    FROM @Adjustments A
    LEFT JOIN TransHeader T WITH(NOLOCK) ON T.ID = A.TransHeaderID
    LEFT JOIN TransDetail TD WITH(NOLOCK) ON TD.ID = A.TransDetailID
    LEFT JOIN Part P WITH(NOLOCK) ON P.ID = A.PartID
    LEFT JOIN Division D WITH(NOLOCK) ON D.ID = A.DivisionID
    LEFT JOIN Warehouse W WITH(NOLOCK) ON W.ID = A.WarehouseID
    ORDER BY P.ItemName, D.DivisionName, W.WarehouseName

    Version Information
    Entered : 10/31/2017
    (view changes)
    1:55 pm
  2. page Control SQL - Adjust Reserved Inventory Levels to Match Orders edited Control SQL - Adjust Reserved Inventory Levels to Match Orders Question How do I adjust the Re…

    Control SQL - Adjust Reserved Inventory Levels to Match Orders
    Question
    How do I adjust the Reserved Inventory Levels in Control?
    Answer
    The Reserved Inventory Quantity in Control is an auto-calculated count that you can not directly adjust.
    The Reserved Quantity is a sum of all estimated usage (minus Actual posted usage) of inventoried parts that are for Orders in a status of WIP & Built.
    The Reserved Quantity is adjusted automatically as you create new orders that use an inventoried part, create usage cards, adjust usage, change order statuses, etc.
    The Reserved Quantity on an inventoried part in most cases should never be a negative value unless you have purposefully created a negative consumption formula.
    If you encounter an issue where the Reserved Quantity is not accurate, you can run an included Utility to correct the number.
    Ask all users to save their work and log out of Control.
    Create a backup of the Control database.
    Open Control and click on Tools > Utilities > Check Reserved Inventory.
    The tool will execute and check for any discrepancies. If any are found then adjusting Inventory Activities will be posted to the InventoryLog table to correct any issues.
    After the tool has been successfully run, it is recommended that you restart your server.
    Version Information
    Entered : 10/31/2017
    Version(s) : Control 4.5+
    See Also
    Backlinks

    (view changes)
    12:34 pm
  3. page Control SQL - Check Inventory On-Order Levels v Ordered POs edited ... SET @EntryDate = GetDate(); DECLARE @Description VARCHAR(255); ... = 'Inventory Adjustmen…
    ...
    SET @EntryDate = GetDate();
    DECLARE @Description VARCHAR(255);
    ...
    = 'Inventory AdjustmentsOnOrder Adjustment by Cyrious';
    DECLARE @Notes VARCHAR(MAX);
    ...
    = 'Inventory AdjustmentsOnOrder Adjustment by Cyrious';
    DECLARE @ModifiedDate DateTime;
    SET @ModifiedDate = GetDate();
    (view changes)
    12:12 pm
  4. page Control SQL - Check Inventory Received Levels On POs edited ... SET @EntryDate = GetDate(); DECLARE @Description VARCHAR(255); ... = 'Inventory Adjustmen…
    ...
    SET @EntryDate = GetDate();
    DECLARE @Description VARCHAR(255);
    ...
    = 'Inventory AdjustmentsReceived Adjustment by Cyrious';
    DECLARE @Notes VARCHAR(MAX);
    ...
    = 'Inventory AdjustmentsReceived Adjustment by Cyrious';
    DECLARE @ModifiedDate DateTime;
    SET @ModifiedDate = GetDate();
    (view changes)
    12:12 pm

Friday, October 27

Wednesday, October 25

Friday, October 6

  1. page Unable to Open MS Outlook edited ... Explanation of Error User Account Control (UAC) is blocking the software from trying to use M…
    ...
    Explanation of Error
    User Account Control (UAC) is blocking the software from trying to use MS Outlook.
    Resolution #1=#1
    Run Outlook
    ...
    As Administrator. JustJust running it
    Click on File | Options
    ...
    Trust Center. ThisThis will open a new windowswindow.
    Choose Programmatic Access.
    ...
    ..." . IfIf the options
    Save.
    Resolution #2
    (view changes)
    1:21 pm

Friday, September 15

  1. page Support Schedule edited ... Independence Day (July 4th) Labor Day Thanksgiving (Thursday and Friday) Christmas Eve C…
    ...
    Independence Day (July 4th)
    Labor Day
    Thanksgiving (Thursday and Friday)
    Christmas Eve
    Christmas Day
    (view changes)
    10:05 am

More