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 SQLBridge 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+
See Also
- Backlinks include_pagepage_componentbacklinks
You could leave a comment if you were logged in.