The stored procedure can be used to insert a part into a line item in Control`

You must supply the following values:

• @TDID - The ID of the TransDetail record you're inserting in to
• @PartID - The ID of the part you're inserting
• @Quantity - The quantity of the part that you're inserting

You can also pass these values for refresh/recomputes. If these are being done on another portion of the query, they aren't needed here

• @RefreshOnSave - Bit, is order being refreshed after insert?
• @RecomputeOnSave - Bit, is order being recomputed after insert?

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.

EXEC dbo.csp_AddPartToLineItem @TDID = 1016, @PartID = 10077, @Quantity = 32.00, @RefreshOnSave = 0, @RecomputeOnSave = 0;

The SQL to create the Part Insert stored procedure follows. This must be run to create the stored procedure before it can be used.

-- =============================================
-- Author:              Cyrious Sofware
-- Create date: Feb-2017
-- Description: This stored procedure adds a part to an existing line item
-- =============================================
CREATE PROCEDURE csp_AddPartToLineItem 
	@TDID INT
	, @PartID INT
	, @Quantity DECIMAL(18,4)
 , @RefreshOnSave BIT = 1
 , @ReComputeOnSave BIT = 0
 , @Debug BIT = 0
AS
BEGIN
	-- DECLARE	@TDID INT = 1016
	-- 	, @PartID INT = 10077
	-- 	, @Quantity DECIMAL(18,4) = 25.0
    --     , @RefreshOnSave BIT = 0
    --     , @ReComputeOnSave BIT = 0
    --     , @Debug BIT = 1
	-- ;
	-- Lookup other key variables
	DECLARE @THID INT
			, @WarehouseID INT
			, @EstimatingWarehouseID INT
			, @PartsXML VARCHAR(MAX)
			, @InventoryID INT
 
	SELECT @THID = TransHeaderID 
			, @WarehouseID = WarehouseID
			, @EstimatingWarehouseID = EstimatingWarehouseID
			, @PartsXML = CONVERT(VARCHAR(MAX), PartStr)
	FROM TransDetail 
	WHERE ID = @TDID;
	SET @InventoryID = (SELECT ID FROM Inventory WHERE PartID = @PartID AND WarehouseID = @WarehouseID);
	-- check if PartsXML is null or empty
	IF (@PartsXML IS NULL) OR (LTRIM(RTRIM(@PartsXML)) IN ('', '', ''))
		SET @PartsXML = '';
	-- Find the location to insert the new Part
	DECLARE @Loc INT = (SELECT PATINDEX('%%', @PartsXML));
	IF (@Debug=1) Print @PartsXML
	IF (@Debug=1) Print @PartID
	IF (@Debug=1) Print @WarehouseID
	IF (@Debug=1) Print @InventoryID
	IF (@Debug=1) PRINT @Loc;
	-- Now build the new PartsXML
	DECLARE @NewPartStartXML VARCHAR(MAX) = '';
	DECLARE @NewPartXML VARCHAR(MAX) = '';
	DECLARE @NewPartEndXML VARCHAR(MAX) = '';
    -- (1056,10300)										(SELECT ID FROM TransPart --Needs to be created?)
	SET @NewPartXML = @NewPartXML + '('+CONVERT(VARCHAR(12),@PartID)+',12014)';
	SET @NewPartXML = @NewPartXML + '('+CONVERT(VARCHAR(12),@WarehouseID)+',12700)';
	SET @NewPartXML = @NewPartXML + '('+CONVERT(VARCHAR(12),@EstimatingWarehouseID)+',12700)';
	SET @NewPartXML = @NewPartXML + '('+CONVERT(VARCHAR(12),@InventoryID)+',12200)';
	SET @NewPartXML = @NewPartXML + ''+CONVERT(VARCHAR(30),@Quantity)+'';
	-- 1						Just assign a number?
	SET @NewPartXML = @NewPartXML + 
				'
				0
				50
				1
				1
				1
				';
 
	SET @PartsXML = LEFT(@PartsXML, @Loc-1) + @NewPartStartXML + @NewPartXML + @NewPartEndXML + SUBSTRING(@PartsXML, @Loc, 999);
	IF (@Debug=1) PRINT @PartsXML;
 
	-- Update the TransDetail
	UPDATE TransDetail
	SET SeqID = SeqID + 1, PartStr = @PartsXML
	WHERE ID = @TDID
	;
	-- Refresh the Line Item and Order
	IF (@RefreshOnSave = 1)
	BEGIN
		EXEC dbo.csf_chapi_refresh @TDID, 10100, -1;
		EXEC dbo.csf_chapi_refresh @THID, 10000, -1;
	END;
	-- Recompute the Order if Desired
	IF (@ReComputeOnSave = 1)
		EXEC dbo.csf_chapi_recompute @THID, 10000, 'Line Item Added'
END;

Contributor: Cyrious Software

Date: 2/24/2017

Version: Control 6.0+