**WARNING: This is not kids stuff. Use of this feature requires expertise in SQL Server and in the Cyrious database structure. It is very possible to irreversibly damage your Cyrious Control database if you make a mistake. Always develop your tests using a separate database on a separate machine. If you are interested in using sql_bridge but are not a SQL guru, please contact a sales or consulting at Cyrious.**

**Note:** This feature requires Control 5.75 or higher .

====== Overview ====== The stored procedure can be used to convert an Estimate to an Order in Control. You must supply one of the following values: * **@EstimateTHID** - The TransHeader.ID of the base estimate. * **@EstimateNumber** - The Estimate Number to convert. * **@EstimateTVID** - The TransVariation.ID of variation you wish to convert. If you don't supply the TransVariation.ID (@TVID), then the default variation will be converted. You may also supply the following values: * **@NewDueDate** - The new due date of the converted order. If not supplied, the current due date is retained. * **@IsServiceTicket** - Set this to 1 to have the estimate converted into a service ticket instead of an order. * **@LockRecords ** -- Set this to 0 to nor lock the existing estimate * **@RefreshRecords** -- Set this to 0 to not refresh the old estimate * **@RecomputeOrder** -- Set this to 1 to force the SSLIP to refresh the new Order (may slow the SSLIP) The following fields are output parameters that may be used to capture the information for the newly converted order: * **@OrderNumber** - The Order Number of the new order. * **@OrderTHID** - The TransHeader.ID of the new order. * **@OrderTVID** - The TransVariation.ID of the new order. The Stored Procedure returns a single row table with the following values: NewOrderNumber, NewTransHeaderID, NewTransVariableID. Notes: The stored procedure requires **[[sql_bridge]]** in order to obtain IDs for the inserted records. sql_bridge is a collection of SQL Server stored prodedures and functions and external modules that can be called to safely insert or update data into the Control database and notify the CHAPI service that data has been updated. The CHAPI service is a controller program that will then notify the SSLIP and all copies of Control about the changes. ====== Example Usage ======

This converts the default Variation for Estimate #647 to an Order.


EXEC csp_ConvertEstimate @EstimateNumber=647
;

This converts the specified variation (and it's associated Estimate header) to an order


EXEC csp_ConvertEstimate @TVID=6432
;

====== Stored Procedure ====== The SQL to create the order import stored procedure follows. This must be run to create the stored procedure before it can be used.


-- =============================================
-- Author:        Cyrious Sofware
-- Create date: July-2016
-- Description:    This stored procedure converts an estimate to an order and closes the estimate.
--                If the estimate has more than one variation, the chosen variation must be supplied.
--                Many of the parameters are option, but if not supplied will used
--                The default behavior.
--
-- Returns:     New TransHeaderID, ClassTypeID, New OrderNumber
-- =============================================
ALTER PROCEDURE csp_ConvertEstimate
    -- You must supply one or more of the following
      @EstimateTHID     INT = NULL      -- The ID of the TransHeader record
    , @EstimateNumber   INT = NULL      -- The Estimate Number
    , @EstimateTVID     INT = NULL      -- You must supply the variation ID if you don't want to use the active variation
    -- You may supply the following optional values
    , @NewDueDate       SMALLDATETIME = NULL
    , @IsServiceTicket  BIT = 0
    , @LockRecords      BIT      = 1        -- Set this to 0 to nor lock the existing estimate
    , @RefreshRecords   BIT      = 1        -- Set this to 0 to not refresh the old estimate
    , @RecomputeOrder   BIT      = 1        -- Set this to 1 to force the SSLIP to refresh the new Order (may slow the SSLIP)
    -- These values can be returned in these variables.  If not passed in, sql_bridge will be used to determine them 
    , @OrderNumber      INT = NULL OUTPUT   -- The New Order Number
    , @OrderTHID        INT = NULL OUTPUT   -- The ID of the new TransHeader record for the order
    , @OrderTVID        INT = NULL OUTPUT   -- The ID of the variation on the order
    , @FirstTDID        INT = NULL OUTPUT   -- The ID of the FIRST new TransDetail record.
    , @FirstTSID        INT = NULL OUTPUT   -- The ID of the FIRST new Shipment record.
    , @FirstALID        INT = NULL OUTPUT   -- The ID of the FIRST new Address Link record.
    -- Thse are used in testing and should not normally be changed
    , @Debug            BIT = 0    -- set @Debug to 1 to display intermediate steps
AS
BEGIN
    SET NOCOUNT ON;
    -- ----------------------------------------------------
    -- Clean up temp tables from previous run if they exist
    -- ----------------------------------------------------
    IF OBJECT_ID('tempdb..#THTemp') IS NOT NULL DROP TABLE #THTemp;
    IF OBJECT_ID('tempdb..#TDTemp') IS NOT NULL DROP TABLE #TDTemp;
    IF OBJECT_ID('tempdb..#TVTemp') IS NOT NULL DROP TABLE #TVTemp;
    IF OBJECT_ID('tempdb..#TSTemp') IS NOT NULL DROP TABLE #TSTemp;
    IF OBJECT_ID('tempdb..#ALTemp') IS NOT NULL DROP TABLE #ALTemp;
    -- ----------------------------------------------------
    -- Variable Declaration
    -- ----------------------------------------------------
    DECLARE @SQL            VARCHAR(MAX);
    DECLARE @StatusID       TINYINT;
    DECLARE @EstimateLocked BIT             = 0;
    -- Define some error variables in case we need them
    DECLARE @DT             SMALLDATETIME  = GetDate();
    DECLARE @ComputerName   VARCHAR(25)    = @@ServerName;
    DECLARE @NewLine        CHAR(2)        = CHAR(10)+CHAR(13);
    DECLARE @ProcName       VARCHAR(50)    = OBJECT_NAME(@@PROCID);
    DECLARE @ErrorMessage    VARCHAR(2048)  = '';
    DECLARE @ErrorNumber     INT            = 99;
    DECLARE @ErrorSeverity   INT            = 15;
    DECLARE @ErrorState      INT            = 0;
    DECLARE @ErrorLine       INT            = 0;
    DECLARE @ErrorProcedure  VARCHAR(200)   = @ProcName;
    -- ----------------------------------------------------
    -- Lookup the correct keys
    -- ----------------------------------------------------
    IF (@EstimateTVID IS NOT NULL)
    BEGIN
        IF (@EstimateTHID IS NOT NULL OR @EstimateNumber IS NOT NULL) 
            SET @ErrorMessage = @ErrorMessage + 'Only one of the parameters @EstimateTVID, @EstimateTHID, and @EstimateNumber may be supplied.; ';
    
        SELECT @EstimateTHID = TH.ID, @EstimateTVID = TV.ID, @EstimateNumber = TH.EstimateNumber, @StatusID = StatusID
        FROM TransHeader TH
        JOIN TransVariation TV ON TV.ParentID = TH.ID
        WHERE (TV.ID = @EstimateTVID);
    END
    ELSE IF (@EstimateTHID IS NOT NULL) 
    BEGIN
        IF (@EstimateNumber IS NOT NULL) 
            SET @ErrorMessage = @ErrorMessage + 'Only one of the parameters @EstimateTVID, @EstimateTHID, and @EstimateNumber may be supplied.; ';
    
        SELECT @EstimateTHID = TH.ID, @EstimateTVID = TV.ID, @EstimateNumber = TH.EstimateNumber, @StatusID = StatusID
        FROM TransHeader TH
        JOIN TransVariation TV ON TV.ParentID = TH.ID
        WHERE (TH.ID = @EstimateTHID) 
        ORDER BY TV.SortIndex  -- The Active Variation has the lowest sort index
    END
    ELSE  
        SELECT @EstimateTHID = TH.ID, @EstimateTVID = TV.ID, @EstimateNumber = TH.EstimateNumber, @StatusID = StatusID
        FROM TransHeader TH
        JOIN TransVariation TV ON TV.ParentID = TH.ID
        WHERE (TH.EstimateNumber = @EstimateNumber AND TH.TransactionType = 2)
        ORDER BY TV.SortIndex  -- The Active Variation has the lowest sort index
    ;
    -- -----------------------------------------------------------
    -- Validate the Input
    -- -----------------------------------------------------------
    IF (@EstimateTHID IS NULL) 
        SET @ErrorMessage = @ErrorMessage + 'Specified Estimate Not Found.; ';
    IF (@StatusID != 11)
        SET @ErrorMessage = @ErrorMessage + 'The specified estimate is not in a Pending status.  You can''t convert closed Estimates; ';
    -- -----------------------------------------------------------
    -- Lock the Estiamte
    -- -----------------------------------------------------------
    IF ((@LockRecords = 1) AND (@ErrorMessage = ''))
    BEGIN
        BEGIN TRY
            EXEC dbo.csf_chapi_lock @EstimateTHID, 10000;
            SET @EstimateLocked = 1;
        END TRY
        BEGIN CATCH
            SET @ErrorMessage = 'Unable to lock Estimate Number '+CONVERT(VARCHAR(16), @EstimateNumber);
            -- Assign variables to error-handling functions that 
            -- capture information for RAISERROR.
            SELECT 
                @ErrorNumber = ERROR_NUMBER(),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE(),
                @ErrorLine = ERROR_LINE(),
                @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
        END CATCH;
    END;   
     
    IF (@ErrorMessage != '')
    BEGIN
        -- Build the message string that will contain original error information.
        SET @ErrorMessage = 'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '+ @ErrorMessage;
        
        RAISERROR 
            (
            @ErrorMessage, 
            @ErrorSeverity, 
            1,               
            @ErrorNumber,    -- parameter: original error number.
            @ErrorSeverity,  -- parameter: original error severity.
            @ErrorState,     -- parameter: original error state.
            @ErrorProcedure, -- parameter: original error procedure name.
            @ErrorLine       -- parameter: original error line number.
            );  
        RETURN; 
    END;    
    -- ----------------------------------------------------
    -- Copy Data into temp tables
    -- ----------------------------------------------------
    SELECT * INTO #THTEMP FROM TransHeader        WHERE ID = @EstimateTHID;
    SELECT * INTO #TVTEMP FROM TransVariation    WHERE ID = @EstimateTVID;
    SELECT * INTO #TDTEMP FROM TransDetail        WHERE TransHeaderID = @EstimateTHID AND VariationID = @EstimateTVID;
    SELECT * INTO #TSTEMP FROM Shipments        WHERE TransHeaderID = @EstimateTHID;
    SELECT * INTO #ALTEMP FROM AddressLink        WHERE (ParentClassTypeID = 10000 AND ParentID = @EstimateTHID )
                                                   OR (ParentClassTypeID = 10700 AND ParentID IN (SELECT ID FROM #TSTEMP));
    -- ----------------------------------------------------
    -- Obtain new IDs using sql_bridge
    -- ----------------------------------------------------
    IF (@OrderTHID      IS NULL )     SET @OrderTHID    = (SELECT dbo.csf_chapi_nextid( 10000, 1));
    IF (@OrderTVID      IS NULL )     SET @OrderTVID    = (SELECT dbo.csf_chapi_nextid( 10400, 1));
    IF (@OrderNumber    IS NULL )     SET @OrderNumber  = (SELECT dbo.csf_chapi_nextnumber( 'OrderNumber', 1));
    IF (@FirstTDID      IS NULL )     SET @FirstTDID    = (SELECT dbo.csf_chapi_nextid( 10100, (SELECT COUNT(*) FROM #TDTemp) ));
    IF (@FirstTSID      IS NULL )     SET @FirstTSID    = (SELECT dbo.csf_chapi_nextid( 10700, (SELECT COUNT(*) FROM #TSTemp) ));
    IF (@FirstALID      IS NULL )     SET @FirstALID    = (SELECT dbo.csf_chapi_nextid(  4002, (SELECT COUNT(*) FROM #ALTemp) ));
    IF (@Debug = 1)
        SELECT @EstimateTHID AS OldTHID, @OrderTHID AS NewTHID,
                @EstimateTVID AS TVID, @OrderTVID AS NewTVID,
                @EstimateNumber AS OldEstimateNumber, @OrderNumber AS NewOrderNumber
    -- ----------------------------------------------------
    -- Create Mapping Tables, since the TD and Shipment IDs may not be sequential
    -- ----------------------------------------------------
    DECLARE @TDIDs TABLE (OldTDID INT PRIMARY KEY, NewTDID INT);
    DECLARE @TSIDs TABLE (OldTSID INT PRIMARY KEY, NewTSID INT, NewShipmentNumber VARCHAR(12));
    DECLARE @ALIDs TABLE (OldALID INT PRIMARY KEY, NewALID INT);
    INSERT INTO @TDIDs
        SELECT T.ID AS OldTDID, (@FirstTDID - 1 + ROW_NUMBER() OVER (ORDER BY T.ID)) AS NewTDID
        FROM #TDTEMP T
    ;
    INSERT INTO @TSIDs
        SELECT T.ID AS OldTSID, (@FirstTSID - 1 + ROW_NUMBER() OVER (ORDER BY T.ID)) AS NewTSID,
            CONVERT(VARCHAR(12), @OrderNumber)+'-SA'+CHAR( 64+ROW_NUMBER() OVER (ORDER BY T.ID) ) AS NewShipmentNumber
        FROM #TSTEMP T
        ORDER BY ShipmentNumber
    ;
    INSERT INTO @ALIDs
        SELECT T.ID AS OldALID, (@FirstALID - 1 + ROW_NUMBER() OVER (ORDER BY T.ID)) AS NewALID
        FROM #ALTEMP T
    ;
    -- ----------------------------------------------------
    -- Update Temp Tables with New IDs and other altered values
    -- ----------------------------------------------------
    -- Update the TransHeader
    UPDATE T
    SET ID = @OrderTHID, ModifiedByUser = @ProcName, ModifiedByComputer = @ComputerName, ModifiedDate = @DT,
        SeqID = 0, IsSystem = 0, IsActive = 1,
        TransactionType = (CASE WHEN @IsServiceTicket=1 THEN 6 ELSE 1 END),
        OrderNumber = @OrderNumber, ServiceTicketNumber = @OrderNumber, OrderCreatedDate = @dt,
        ProductionNotes = 'SQL Cloned from Estimate Number '+CONVERT(VARCHAR(12), EstimateNumber)+';  '+COALESCE(ProductionNotes, ''),
        ProductionNotesTemplate = 'SQL Cloned from Estimate Number '+CONVERT(VARCHAR(12), EstimateNumber)+';  '+COALESCE(ProductionNotesTemplate, ''),
        DueDate = COALESCE(@NewDueDate, DueDate),
        StatusID = 1, StatusText = 'WIP',
        ClonedFromTransactionNumber = @EstimateNumber, ClonedFromTransactionType = 2
    FROM #THTEMP T
    ;
    -- Update the TransVariation
    UPDATE T
    SET ID = @OrderTVID, ModifiedByUser = @ProcName, ModifiedByComputer = @ComputerName, ModifiedDate = @DT,
        SeqID = 0, IsSystem = 0, IsActive = 1,
        TransactionType = 1, ParentID = @OrderTHID
    FROM #TVTEMP T
    ;
    -- Update the TransDetails
    UPDATE T
    SET ID = I.NewTDID, ModifiedByUser = @ProcName, ModifiedByComputer = @ComputerName, ModifiedDate = @DT,
        SeqID = 0, IsSystem = 0, IsActive = 1,
        TransactionType = 1, TransHeaderID = @OrderTHID, TransHeaderTransNumber = @OrderNumber,
        VariationID = @OrderTVID,
        ParentID = (CASE WHEN ParentClassTypeID = 10100
                         THEN (SELECT I2.NewTDID FROM @TDIDs I2 WHERE I2.OldTDID = ParentID)
                          ELSE @OrderTHID
                    END),
        DueDate = COALESCE(@NewDueDate, DueDate),
        PartStr.modify('delete /Parts/Part/ID'),                -- strip embedded IDs are stored in the XML
        ParameterStr.modify('delete /Parameters/Parameter/ID'), -- strip embedded IDs are stored in the XML
        QuantityShipped = 0
    FROM #TDTEMP T
    JOIN @TDIDs I ON T.ID = I.OldTDID
    ;
    -- Update the Shipment Info
    UPDATE T
    SET ID = I.NewTSID, ModifiedByUser = @ProcName, ModifiedByComputer = @ComputerName, ModifiedDate = @DT,
        SeqID = 0, IsSystem = 0, IsActive = 1,
        TransHeaderID = @OrderTHID,
        -- ShipLineItemsXML = Redirect(ShipLineItemsXML),               -- necessary to rework the TDIDs in the XML
        IsShipped = 0,
        ShipmentNumber = I.NewShipmentNumber
    FROM #TSTEMP T
    JOIN @TSIDs I ON T.ID = I.OldTSID
    ;
    -- Updating the ShipLineItemsXML is the hardest part here ...
    -- The approach is to run this string replacement for each possible TransDetail
    UPDATE T
    SET ShipLineItemsXML =
            REPLACE(CONVERT(VARCHAR(MAX),ShipLineItemsXML),
                    '('+rtrim(ltrim(CONVERT(VARCHAR(12), I.OldTDID)))+',10100)',
                    '('+CONVERT(VARCHAR(12), I.NewTDID)+',10100)')
    FROM #TSTEMP T
    JOIN @TDIDs I ON 1 = 1
    WHERE CHARINDEX( '('+CONVERT(VARCHAR(12), I.OldTDID)+',10100)', CONVERT(VARCHAR(MAX),ShipLineItemsXML )) > 1
    ;
    -- Update the Address Links
    UPDATE T
    SET ID = I.NewALID, ModifiedByUser = @ProcName, ModifiedByComputer = @ComputerName, ModifiedDate = @DT,
        SeqID = 0, IsSystem = 0, IsActive = 1,
        ParentID = (CASE WHEN ParentClassTypeID = 10000 -- Order Address Link
                         THEN @OrderTHID
                         WHEN ParentClassTypeID = 10700 -- Shipping Address LInk
                         THEN (SELECT I2.NewTSID FROM @TSIDs I2 WHERE I2.OldTSID = ParentID)
                         ELSE NULL  -- Shouldn't be here!
                    END),
        IsMaster = 0
    FROM #ALTEMP T
    JOIN @ALIDs I ON T.ID = I.OldALID
    ;
    IF (@Debug=1)
    BEGIN
        SELECT * FROM #THTEMP;
        SELECT * FROM #TVTEMP;
        SELECT * FROM #TDTEMP;
        SELECT * FROM #TSTEMP;
        SELECT * FROM #ALTEMP;
        SELECT * FROM @TDIDs;
        SELECT * FROM @TSIDs;
    END;
    -- ----------------------------------------------------
    -- Insert the Data back into the Database and UPdate the Estimate
    -- ----------------------------------------------------
    BEGIN TRANSACTION
    BEGIN TRY
        INSERT INTO TransHeader       SELECT * FROM #THTEMP;
        INSERT INTO TransVariation    SELECT * FROM #TVTEMP;
        -- Because TransDetail contains computed fields, we can't just insert all the records into it blindly
        -- First, we need to generate a comma delimited Field List of non-computed fields
        DECLARE @fl VARCHAR(MAX);
        SET @fl =
            ( SELECT SUBSTRING(
                (
                    SELECT ', '+C.Name AS [text()]
                    FROM sys.TABLES T
                    JOIN sys.COLUMNS C ON T.object_id = C.object_id
                    WHERE T.Name = 'TransDetail'
                    AND C.is_computed = 0
                    ORDER BY C.column_id
                    FOR XML PATH ('')
                ), 3, 9999)
            );
        IF (@debug=1) print @fl;
        SET @SQL = 'INSERT INTO TransDetail ('+@fl+')  SELECT '+@fl+' FROM #TDTemp;';
        EXEC( @SQL );
        -- now continue with Shipments and AddressLink
        INSERT INTO Shipments       SELECT * FROM #TSTEMP;
        INSERT INTO AddressLink     SELECT * FROM #ALTEMP;
        -- ----------------------------------------------------
        -- Close the Estimate
        -- ----------------------------------------------------
        UPDATE TransHeader
        SET SeqID = SeqID + 1, ModifiedByUser = @ProcName, ModifiedByComputer = @ComputerName, ModifiedDate = @DT,
            StatusID = 13, StatusText = 'Converted', ConvertedDate = @DT,
            OrderNumber = @OrderNumber
        WHERE ID = @EstimateTHID
        ;
        -- Make the changes permanent
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- ----------------------------------------------------
        -- Handle Any Errors
        -- ----------------------------------------------------
        IF (@EstimateLocked=1)
            EXEC dbo.csf_chapi_unlock @EstimateTHID, 10000;
        ROLLBACK TRANSACTION;
        SELECT @ErrorMessage    = ERROR_MESSAGE(),
               @ErrorSeverity   = ERROR_SEVERITY(),
               @ErrorState      = ERROR_STATE(),
			   @ErrorNumber		= ERROR_NUMBER(),
			   @ErrorState      = ERROR_STATE(),
			   @ErrorLine       = ERROR_LINE();
        -- Build the message string that will contain original error information.
        SET @ErrorMessage = 'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '+ @ErrorMessage;
        
        RAISERROR 
            (
            @ErrorMessage, 
            @ErrorSeverity, 
            1,               
            @ErrorNumber,    -- parameter: original error number.
            @ErrorSeverity,  -- parameter: original error severity.
            @ErrorState,     -- parameter: original error state.
            @ErrorProcedure, -- parameter: original error procedure name.
            @ErrorLine       -- parameter: original error line number.
            );  
        RETURN;
    END CATCH;
    -- ----------------------------------------------------
    -- Now Refresh and Recompute
    -- ----------------------------------------------------
    IF (@EstimateLocked = 1)
        EXEC dbo.csf_chapi_unlock @EstimateTHID, 10000;
    IF (@RefreshRecords = 1)
    BEGIN
        EXEC dbo.csf_chapi_refresh @EstimateTHID, 10000, -1;
        EXEC dbo.csf_chapi_refresh @EstimateTVID, 10400, -1;
    END;
    IF (@RecomputeOrder = 1)
        EXEC dbo.csf_chapi_recompute @OrderTHID, 10000, 'Estimate converted - SQL Recompute.';
    -- ----------------------------------------------------
    -- Return Results
    -- ----------------------------------------------------
    SELECT @OrderTHID AS TransHeaderID, 10000 as ClassTypeID, @OrderNumber AS OrderNumber ;
END;

===== Source ===== Contributor: Cyrious Software Date: 5/2016 Version: Control 5.7+