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 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 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 inserts a single part with a quantity of 32 onto an existing line item

code_formatsql

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

code

Stored Procedure

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

code_formatsql

–

– 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

  1. - DECLARE @TDID INT = 1016
  1. - , @PartID INT = 10077
  1. - , @Quantity DECIMAL(18,4) = 25.0
  1. - , @RefreshOnSave BIT = 0
  1. - , @ReComputeOnSave BIT = 0
  1. - , @Debug BIT = 1
  1. - ;
  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);
  1. - check if PartsXML is null or empty
IF (@PartsXML IS NULL) or (LTRIM(RTRIM(@PartsXML)) in ('', '', ''))
	SET @PartsXML = '';
  1. - 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;
  1. - Now build the new PartsXML
DECLARE @NewPartStartXML Varchar(max) = '';
DECLARE @NewPartXML Varchar(max) = '';
DECLARE @NewPartEndXML Varchar(max) = '';
  1. - (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. - 1 Just assign a number?
SET @NewPartXML = @NewPartXML + 
			'
			50
			1
			1
			1
			';
SET @PartsXML = LEFT(@PartsXML, @Loc-1) + @NewPartStartXML + @NewPartXML + @NewPartEndXML + SUBSTRING(@PartsXML, @Loc, 999);
IF (@Debug=1) PRINT @PartsXML;
  1. - Update the TransDetail
UPDATE TransDetail
SET SeqID = SeqID + 1, PartStr = @PartsXML
WHERE ID = @TDID
;
  1. - 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;
  1. - Recompute the Order if Desired
IF (@ReComputeOnSave = 1)
	EXEC dbo.csf_chapi_recompute @THID, 10000, 'Line Item Added'

END;

code

Source

Contributor: Cyrious Software

Date: 2/24/2017

Version: Control 6.0+

See Also

You could leave a comment if you were logged in.