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
EXEC csp_ConvertEstimate @EstimateNumber=647 ;
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+