Recent Changes

Thursday, October 4

Tuesday, September 25

  1. page Control SQL - Check Inventory Received Levels On POs edited ... The second query will post any adjustment necessary to Received while not posting any adjustme…
    ...
    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.
    All users should be out of the system when running this query
    Restart Chapi and the SSLIP otherwise duplicate Journal records will be created.

    SQL Query to Reduce Received, Increase Billed Qty
    DECLARE @CommitChanges BIT;
    (view changes)
    8:49 am

Monday, September 3

  1. user_add SRuiz SRuiz joined support
    4:29 pm

Tuesday, August 21

  1. user_add BTeekel BTeekel joined support
    8:23 am

Friday, August 10

  1. 12:01 pm
  2. 11:58 am

Sunday, July 15

  1. page Control SQL - Check Inventory Received Levels On POs edited ... Risk of Data Corruption if Run Improperly High. Inventory data is modified in this query. Do …
    ...
    Risk of Data Corruption if Run Improperly
    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
    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 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.
    SQL
    Query to Reduce Received, Increase Billed Qty
    DECLARE @CommitChanges BIT;
    SET @CommitChanges = 0;
    ...
    SELECT New_JournalID, -1, 20530, @ModifiedByUser, @ModifiedByComputer, @ModifiedDate, 0, 0, 1,
    -(ActualReceivedOnly - CurrentReceivedOnly), ActualReceivedOnly - CurrentReceivedOnly, 0, 0,
    0, 0, PartID, 12014,
    InventoryID, WarehouseID, DivisionID, 0, 1
    FROM @T T
    ;
    END
    ELSE
    SELECT InventoryID, PartID, DivisionID, WarehouseID, CurrentReceivedOnly, ActualReceivedOnly FROM @T
    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,
    PartID INT,
    DivisionID INT,
    WarehouseID INT,
    CurrentReceivedOnly FLOAT,
    ActualReceivedOnly FLOAT,
    New_JournalID INT )
    ;
    -- Insert all of the inventory items into the temp table
    INSERT INTO @T
    (InventoryID, PartID, DivisionID, WarehouseID)
    SELECT I.ID, I.PartID, I.DivisionID, I.WarehouseID
    FROM Inventory I
    LEFT JOIN Part P ON P.ID = I.PartID
    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
    LEFT JOIN ( SELECT InventoryID
    , ROUND( SUM( ISNULL( QuantityReceivedOnly, 0 ) ), 4 ) AS QuantityReceivedOnly
    FROM InventoryLog
    WHERE InventoryID IS NOT NULL
    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);
    INSERT INTO @ActualReceivedOnly
    SELECT I.ID InventoryID,
    SUM( CASE WHEN TD.UnitID = P.UnitID THEN TD.POQuantityReceivedOnly
    ELSE ( TD.POQuantityReceivedOnly * ISNULL(IC.FxUpper / IC.FxLower, 1 ) )
    END ) POQuantityReceivedOnly
    FROM ( SELECT TransHeaderID,
    ( CASE WHEN ItemClassTypeID = 12076 THEN (SELECT TOP 1 PartID FROM CatalogItem WHERE ID = ItemID)
    ELSE ItemID
    END ) PartID,
    UnitID,
    Quantity,
    BillQuantity,
    ( ISNULL( Quantity, 0 ) - ISNULL( BillQuantity, 0 ) ) AS POQuantityReceivedOnly,
    WarehouseID
    FROM VendorTransDetail
    WHERE ID > 0
    AND ItemID IS NOT NULL
    AND StationID IN (108, 109) -- Received, Partially Received
    ) TD
    JOIN TransHeader TH ON TH.ID = TD.TransHeaderID
    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 ( SELECT PartID,
    UnitID,
    PartInventoryConversion.ConversionFormula,
    CASE
    WHEN PartInventoryConversion.ConversionFormula IS NULL THEN 1.00
    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)
    END FxUpper,
    CASE
    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)
    ELSE 1.00
    END FxLower
    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
    ;
    UPDATE @T
    SET ActualReceivedOnly = ISNULL( CR.Amount, 0 )
    FROM @T T
    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
    ;
    IF @CommitChanges = 1
    BEGIN
    -- Compute the IDs for the new records
    DECLARE @LastJournalID INT;
    SET @LastJournalID = (SELECT MAX(ID) FROM Journal);
    UPDATE T
    SET New_JournalID = RowNum + @LastJournalID
    FROM
    (
    SELECT x.New_JournalID,
    ROW_NUMBER() OVER(ORDER BY x.InventoryID) AS RowNum
    FROM @T x
    ) AS T
    ;
    -- Insert Journal entry
    INSERT INTO Journal
    (ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive,
    JournalActivityType, JournalActivityText, Description, Notes, StartDateTime, EndDateTime, CompletedDateTime,
    IsSummary, IsDetail, SummaryAmount, DetailAmount, StartGLGroupID, EndGLGroupID, IsVoided,
    QueryStartDateTime, QueryEndDateTime, ReminderPrompt, PartID, PartClassTypeID,
    ActivityType, ActivityTypeText, IsBillable, UseActualTime, BillingType, TotalBilledTime, DivisionID, HasCalendarLinks)
    SELECT New_JournalID, -1, 20530, @ModifiedByUser, @ModifiedByComputer, @ModifiedDate, 0, 0, 1,
    17, 'Part Usage', @Description, @Notes, @EntryDate, @EntryDate, @EntryDate,
    1, 1, 0, 0, NULL, NULL, 0,
    @EntryDate, @EntryDate, 0, PartID, 12014,
    43, 'Inventory Edited', 0, 0, 0, 0, DivisionID, 0
    FROM @T
    ;
    -- Insert InventoryLog entry
    INSERT INTO InventoryLog
    (ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive,
    QuantityBilled, QuantityReceivedOnly, QuantityReserved, QuantityOnOrder,
    UnitCost, Cost, PartID, PartClassTypeID,
    InventoryID, FromWarehouseID, FromDivisionID, SortIndex, UnitID)
    SELECT New_JournalID, -1, 20530, @ModifiedByUser, @ModifiedByComputer, @ModifiedDate, 0, 0, 1,
    0, ActualReceivedOnly - CurrentReceivedOnly, 0, 0,

    0, 0, PartID, 12014,
    InventoryID, WarehouseID, DivisionID, 0, 1
    ...
    SELECT InventoryID, PartID, DivisionID, WarehouseID, CurrentReceivedOnly, ActualReceivedOnly FROM @T
    Version Information
    Revised : 11/201407/2018
    Version : Control 5.16.1
    Related SQLs
    Backlinks
    (view changes)
    8:53 pm

Monday, July 9

  1. 11:21 am

Sunday, July 1

  1. 8:38 pm

Thursday, June 7

More