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.

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 sql_bridge in order to obtain IDs for the inserted records. sql_bridge is a collection of SQL Server stored procedures 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.

This creates a basic part category called Angle Iron under Materials.

EXEC dbo.csp_ImportPartCategory
        @CategoryName = 'Angle Iron'
        , @CategoryParentName = 'Materials'

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  -- <ID,  int, >
                , -1           -- ,<StoreID, int,>
                , 12035         -- ,<ClassTypeID, int,>
                , @ProcName  -- ,<ModifiedByUser, nvarchar(25),>
                , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),>
                , @DT    -- ,<ModifiedDate, datetime,>
                , 0            -- ,<SeqID, int,>
                , 0            -- ,<IsSystem, bit,>
                , 1            -- ,<IsActive, bit,>
                , @CategoryParentID        -- <ParentID,  int, >
                , -1        -- <ParentStoreID,  int, >
                , 12035        -- <ParentClassTypeID,  int, >
                , @CategoryName        -- <ElementName,  varchar(100), >
                , @Description        -- <Description,  varchar(50), >
                , 0        -- <SortIndex,  decimal(18, 4), >
                , NULL        -- <LinkID,  int, >
                , NULL        -- <LinkStoreID,  int, >
                , NULL        -- <LinkClassTypeID,  int, >
                , NULL        -- <ItemPrefix,  varchar(25), >
                , NULL        -- <IncomeID,  int, >
                , NULL        -- <IncomeStoreID,  int, >
                , 8001        -- <IncomeClassTypeID,  int, >
                , NULL        -- <StartDate,  smalldatetime, >
                , NULL        -- <EndDate,  smalldatetime, >
                , NULL        -- <FamilyID,  int, >
                , NULL        -- <FamilyStoreID,  int, >
                , NULL        -- <FamilyClassTypeID,  int, >
                , NULL        -- <GLDepartmentID,  int, >
                , NULL        -- <GLDepartmentClassTypeID,  int, >
                , 0        -- <GLDepartmentIDOV,  bit, >
                , NULL        -- <DefaultIncomeIDOV,  bit, >
                , @ExpenseAccountID        -- <ExpenseID,  int, >
                , 8001        -- <ExpenseClassTypeID,  int, >
                , @PartTypeID        -- <PartType,  int, >
                , @UseCategoryDefaults        -- <UseCategoryDefaults,  bit, >
                , @TrackCosts        -- <DefaultTrackCosts,  bit, >
                , @AccrueCosts        -- <DefaultAccrueCosts,  bit, >
                , @TrackInventory        -- <DefaultTrackInventory,  bit, >
                , NULL        -- <ComputedCostID,  int, >
                , NULL        -- <ComputedCostClassTypeID,  int, >
                , @UDFLayoutSettings        -- <UDFLayoutSettings,  varchar(max), >
                , @LocalTags        -- <LocalTags,  varchar(max), >
                , @AccrualAccountID        -- <DefaultAssetAccountID,  int, >
                , 8001        -- <DefaultAssetAccountClassTypeID,  int, >
                , NULL        -- <MaterialExpenseID,  int, >
                , NULL        -- <MaterialExpenseClassTypeID,  int, >
                , NULL        -- <LaborExpenseID,  int, >
                , NULL        -- <LaborExpenseClassTypeID,  int, >
                , NULL        -- <EquipmentExpenseID,  int, >
                , NULL        -- <EquipmentExpenseClassTypeID,  int, >
                , NULL        -- <OutsourceExpenseID,  int, >
                , NULL        -- <OutsourceExpenseClassTypeID,  int, >
                , NULL        -- <OtherExpenseID,  int, >
                , NULL        -- <OtherExpenseClassTypeID,  int, >
                , NULL        -- <FreightExpenseID,  int, >
                , NULL        -- <FreightExpenseClassTypeID,  int, >
                , NULL        -- <LocationExpenseID,  int, >
                , NULL        -- <LocationExpenseClassTypeID,  int, >
                , (CASE WHEN @PartTypeID = 0 THEN 1 ELSE 0 END)        -- <ApplyMaterialTemplate,  bit, >
                , (CASE WHEN @PartTypeID = 1 THEN 1 ELSE 0 END)        -- <ApplyLaborTemplate,  bit, >
                , (CASE WHEN @PartTypeID = 2 THEN 1 ELSE 0 END)        -- <ApplyEquipmentTemplate,  bit, >
                , (CASE WHEN @PartTypeID = 3 THEN 1 ELSE 0 END)        -- <ApplyOutsourceTemplate,  bit, >
                , (CASE WHEN @PartTypeID = 4 THEN 1 ELSE 0 END)        -- <ApplyOtherTemplate,  bit, >
                , (CASE WHEN @PartTypeID = 5 THEN 1 ELSE 0 END)        -- <ApplyFreightTemplate,  bit, >
                , (CASE WHEN @PartTypeID = 6 THEN 1 ELSE 0 END)        -- <ApplyLocationTemplate,  bit, >
                , 10        -- <DivisionID,  int, >
                , 0        -- <IsDefault,  bit, >
                , NULL        -- <ExpenseIDOV,  bit, >
                , @ResellProductID        -- <ResellProductID,  int, >
                , (CASE WHEN @ResellProductID IS NULL THEN 0 ELSE 1 END)    -- <ResellProductIDOV,  bit, >
                , @ResellVariableID        -- <ResellProductVariableID,  int, >
        );
 
    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;

Contributor: Cyrious Software
Date: 9/2016
Version: Control 5.7+