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 import a PART CATEGORY into Control`
You must supply the following values:
- @CategoryName - The name of the new part
You can specify either the Parent Category Name or ID. If you specify none,
the category will be a top-level category.
- @CategoryParentName -
- @CategoryParentID -
By default, the information is copied from the parent category unless you specify
the information below.
- @CopyDefaultsFromParentCategory - Set this to 0 to not copy non-specified values from the parent category
- @Description - The description for this part.
- @UseCategoryDefaults - Set this to zero to override the inventory and accounting options
- @TrackCosts - Set this to 0 if the cost is not used
- @ExpenseAccountID - The GLAccount.ID of the cost expense account for this part
- @TrackInventory - Set this to 1 to track inventory for this part (it must be a material part)
- @AccrueCosts - Set this to 1 to accrue inventory on the balance sheet (@TrackInventory must be 1)
- @AccrualAccountID - The GLAccount.ID of the inventory asset account for this part
- @UDFLayoutSettings - The value for any UDF Layout Settings. If null* copies part category automatically * @LocalTags - The value for LocalTags. If null* copies part category automatically
- @ResellProductID - The resell-product to use when this part is added
- @ResellVariableID - The resell-product variable ID to use for the selected part
The following fields are output parameters that may be used to capture the information for the newly converted order:
- @PartCategoryID - The ID of the New Part Category
The Stored Procedure returns a single row table with the following values: PartID, PartClassTypeID
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 creates a basic part category called Angle Iron under Materials
EXEC dbo.csp_ImportPartCategory
@CategoryName = 'Angle Iron'
, @CategoryParentName = 'Materials'
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.
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name = 'csp_ImportPartCategory')
DROP PROCEDURE csp_ImportPartCategory;
GO
–
– Author: Cyrious Sofware
– Create date: September-2016
– Description: This stored procedure adds a part category
–
– Example Usage
–
– EXEC dbo.csp_ImportPartCategory
– @CategoryName = 'Angle Iron'
– , @CategoryParentName = 'Materials'
–
– Returns: Category.ID and ClassTypeID of the new Part Category
–
CREATE PROCEDURE csp_ImportPartCategory
- - The following values are required
@CategoryName VARCHAR(100) -- The name of the new part category
- - You must supply either the parent category name or parent category ID. Set the name to NULl for top level
, @CategoryParentName VARCHAR(100) = 'Materials' -- The name of the part category this value belongs under
, @CategoryParentID INT = NULL -- The ID of the part category this value belongs under
- - Optional Configuraiton Settings
, @CopyDefaultsFromParentCategory BIT = 1 -- Set this to 0 to not copy non-specified values from the parent category
- - Optional Accounting Settings
, @Description VARCHAR(1024) = NULL -- The description for this part.
, @UseCategoryDefaults BIT = NULL -- Set this to zero to override the inventory and accounting options
, @PartTypeID INT = NULL -- Set this to the default Part Type for the category
, @TrackCosts BIT = NULL -- Set this to 0 if the cost is not used
, @ExpenseAccountID INT = NULL -- The GLAccount.ID of the cost expense account for this part
, @TrackInventory BIT = NULL -- Set this to 1 to track inventory for this part (it must be a material part)
, @AccrueCosts BIT = NULL -- Set this to 1 to accrue inventory on the balance sheet (@TrackInventory must be 1)
, @AccrualAccountID INT = NULL -- The GLAccount.ID of the inventory asset account for this part
, @UDFLayoutSettings VARCHAR(MAX) = NULL -- The value for any UDF Layout Settings. If null, copies part category automatically
, @LocalTags VARCHAR(1024) = NULL -- The value for LocalTags. If null, copies part category automatically
, @ResellProductID INT = NULL -- The resell-product to use when this part is added
, @ResellVariableID INT = NULL -- The resell-product variable ID to use for the selected part
- - Some Developer Options
, @Debug BIT = 1 -- Set to 1 to output additional information (helpful while developing)
, @SuppressOutput BIT = 0 -- Set to 1 if you want the stored procedure to output nothing
, @RefreshOnSave BIT = 1 -- Set to 0 to not refresh upon save
- - Some OUTPUT Parameters in case the caller wants any of these value back
, @PartCategoryID INT = NULL OUTPUT -- New Part Category ID.
AS
BEGIN
SET NOCOUNT ON;
- - —————————————————-
- - Variable Declaration
- - —————————————————-
DECLARE @DT SMALLDATETIME = GetDate()
, @ComputerName VARCHAR(25) = @@ServerName
, @NewLine CHAR(2) = CHAR(10)+CHAR(13)
, @ProcName VARCHAR(50) = OBJECT_NAME(@@PROCID);
DECLARE @ErrorMessage VARCHAR(2048) = ''
, @ErrorNumber INT = 99
, @ErrorSeverity INT = 15
, @ErrorState INT = 0
, @ErrorLine INT = 0
, @ErrorProcedure VARCHAR(200) = @ProcName;
- - —————————————————-
- - Lookup the correct keys
- - —————————————————-
IF (@CategoryParentID IS NULL)
SET @CategoryParentID = (SELECT ID FROM PricingElement WHERE ElementName = @CategoryParentName AND ClassTypeID = 12035 );
IF ( (@CategoryParentID IS NOT NULL) AND (@CopyDefaultsFromParentCategory = 1))
SELECT @PartTypeID = COALESCE( @PartTypeID , PartType )
, @Description = COALESCE( @Description , (CASE WHEN Description = ElementName THEN NULL ELSE Description END) )
, @UDFLayoutSettings = COALESCE( @UDFLayoutSettings , CONVERT(VARCHAR(MAX), UDFLayoutSettings) )
, @LocalTags = COALESCE( @LocalTags , LocalTags )
, @UseCategoryDefaults = COALESCE( @UseCategoryDefaults , UseCategoryDefaults )
, @TrackCosts = COALESCE( @TrackCosts , DefaultTrackCosts )
, @ExpenseAccountID = COALESCE( @ExpenseAccountID , ExpenseID )
, @TrackInventory = COALESCE( @TrackInventory , DefaultTrackInventory )
, @AccrueCosts = COALESCE( @AccrueCosts , DefaultAccrueCosts )
, @AccrualAccountID = COALESCE( @AccrualAccountID , DefaultAssetAccountID )
, @ResellProductID = COALESCE( @ResellProductID , ResellProductID )
, @ResellVariableID = COALESCE( @ResellVariableID , ResellProductVariableID )
FROM PricingElement
WHERE ID = @CategoryParentID
AND ClassTypeID = 12035
- - Else set default values if not passed in
ELSE
SELECT @PartTypeID = COALESCE( @PartTypeID , 0 )
, @Description = COALESCE( @Description , @CategoryName )
, @UseCategoryDefaults = COALESCE( @UseCategoryDefaults , 1 )
, @TrackCosts = COALESCE( @TrackCosts , 1 )
, @TrackInventory = COALESCE( @TrackInventory , 0 )
, @AccrueCosts = COALESCE( @AccrueCosts , 0 )
;
- - ———————————————————–
- - Validate the Input
- - ———————————————————–
IF (@CategoryParentID IS NULL)
SET @ErrorMessage = @ErrorMessage + 'Parent Category Not Specified or Found".; ';
IF (@PartTypeID IS NULL)
SET @ErrorMessage = @ErrorMessage + '@PartTypeID not specified.; ';
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;
- - —————————————————-
- - Obtain new IDs using SQL Bridge
- - —————————————————-
IF (@PartCategoryID IS NULL ) SET @PartCategoryID = (SELECT dbo.csf_chapi_nextid( 12035, 1));
- - —————————————————-
- - Create the Records
- - —————————————————-
BEGIN TRY -- We don't need a transaction since there is only one record to insert
INSERT INTO PricingElement
(ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive
, ParentID, ParentStoreID, ParentClassTypeID, ElementName, Description, SortIndex, LinkID, LinkStoreID
, LinkClassTypeID, ItemPrefix, IncomeID, IncomeStoreID, IncomeClassTypeID, StartDate, EndDate, FamilyID
, FamilyStoreID, FamilyClassTypeID, GLDepartmentID, GLDepartmentClassTypeID, GLDepartmentIDOV
, DefaultIncomeIDOV, ExpenseID, ExpenseClassTypeID, PartType, UseCategoryDefaults, DefaultTrackCosts
, DefaultAccrueCosts, DefaultTrackInventory, ComputedCostID, ComputedCostClassTypeID, UDFLayoutSettings
, LocalTags, DefaultAssetAccountID, DefaultAssetAccountClassTypeID, MaterialExpenseID, MaterialExpenseClassTypeID
, LaborExpenseID, LaborExpenseClassTypeID, EquipmentExpenseID, EquipmentExpenseClassTypeID, OutsourceExpenseID
, OutsourceExpenseClassTypeID, OtherExpenseID, OtherExpenseClassTypeID, FreightExpenseID
, FreightExpenseClassTypeID, LocationExpenseID, LocationExpenseClassTypeID, ApplyMaterialTemplate
, ApplyLaborTemplate, ApplyEquipmentTemplate, ApplyOutsourceTemplate, ApplyOtherTemplate, ApplyFreightTemplate
, ApplyLocationTemplate, DivisionID, IsDefault, ExpenseIDOV, ResellProductID, ResellProductIDOV
, ResellProductVariableID)
VALUES (
@PartCategoryID --
, -1 -- ,
, 12035 -- ,
, @ProcName -- ,
, @ComputerName -- ,
, @DT -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, @CategoryParentID --
, -1 --
, 12035 --
, @CategoryName --
, @Description --
, 0 --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, 8001 --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, 0 --
, NULL --
, @ExpenseAccountID --
, 8001 --
, @PartTypeID --
, @UseCategoryDefaults --
, @TrackCosts --
, @AccrueCosts --
, @TrackInventory --
, NULL --
, NULL --
, @UDFLayoutSettings --
, @LocalTags --
, @AccrualAccountID --
, 8001 --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, (case when @PartTypeID = 0 THEN 1 ELSE 0 END) --
, (case when @PartTypeID = 1 THEN 1 ELSE 0 END) --
, (case when @PartTypeID = 2 THEN 1 ELSE 0 END) --
, (case when @PartTypeID = 3 THEN 1 ELSE 0 END) --
, (case when @PartTypeID = 4 THEN 1 ELSE 0 END) --
, (case when @PartTypeID = 5 THEN 1 ELSE 0 END) --
, (case when @PartTypeID = 6 THEN 1 ELSE 0 END) --
, 10 --
, 0 --
, NULL --
, @ResellProductID --
, (CASE WHEN @ResellProductID IS NULL THEN 0 ELSE 1 END) --
, @ResellVariableID --
);
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
- - Build the message string that will contain original
- - error information.
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
- - Raise an error: msg_str parameter of RAISERROR will contain
- - the original error information.
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.
);
END CATCH;
- - ——————————————————–
- - Refresh the Part Category
- - ———————————————————-
IF (@RefreshOnSave=1)
BEGIN
EXEC dbo.csf_chapi_refresh @PartCategoryID, 12035, -1;
EXEC dbo.csf_chapi_refresh @CategoryParentID, 12035, -1;
END;
- - ———————————————————-
- - Return the New Part ID
- - ———————————————————-
SELECT @PartCategoryID, 12035
END;
END;
Source
Contributor: Cyrious Software
Date: 9/2016
Version: Control 5.7+
See Also
- Backlinks include_pagepage_componentbacklinks
You could leave a comment if you were logged in.