SQL Stored Procedure - Import Part

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 into Control`

You must supply the following values:

  • @PartName - The name of the new part

You can start with a copy an existing part by specifying one of the following two values

  • @CopyFromPartID - The ID of the Part you want to clone
  • @CopyFromPartName - The name of the part you want to clone

If you don't supply a CopyFromPart, you must supply the following information:

  • PartTypeID – The Type of Part

0 = Material 4 = Other

1 = Labor 5 = Freight

2 = Equipment 6 = Location

3 = Outsource

  • CategoryID - The ID of the Part Category. Use this value or the CategoryName, but not both.
  • CategoryName - The Name of the Part Category. Use this value or the CategoryID, but not both.

The following information is optional. If you supply values, it will override the default or the CopyFromPart.

  • @Description - The description for this part.
  • @SKU - The SKU for this part
  • @DefaultStationID - The default station this part is used in.
  • @UDFXML - The value for any UDFs set for this product. Ignored if @CopyUDFsFromPartID is not null
  • @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
  • @UnitText - The default unit for this part. The value must be from the _Unit table. Defaults to 'Each'
  • @DisplayUnitText - The display unit for this part. If not specified* the @UnitText is used.
  • @DisplayUnitsInUnit - How many display units are in 1 standard unit. Leave blank to use the default.
  • @UnitCost - The number for the cost per unit.
  • @UnitCostFormula - The formula for the cost per unit.
  • @CostMultiplierFormula - The number or formula for the price multiplier. Set to a Non-null to override the default multiplier
  • @PriceMultiplierFormula - The number or formula for the price. Set to a non-null to use the price
  • @ScrapFactor - The scrap factor to add. Set to a non-null value to enabled
  • @RoundingFactor - The scrap rounding factor. Set to 1.0 for whole sheet* 0.5 for half-sheet* etc. Defaults to 1
  • @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
  • @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:

  • @PartID - The ID of the New Part

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.

Example Usage

This copies and existing parts and adjusts some of the values

EXEC csp_ImportPart
          @PartName = 'High Performance Magnetic Roll'
        , @CopyFromPartName = 'Magentic Roll'
        , @UnitCost = 4.25
        , @UnitText = 'foot'
        , @DisplayUnitText = 'sq ft'
        , @DisplayUnitsInUnit = 1.5
;

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: May-2016
-- Description: This stored procedure adds a part
--
-- Returns:     Part.ID of the new Part
-- =============================================
ALTER PROCEDURE csp_ImportPart
 
    -- The following values are required
      @PartName             VARCHAR(100)        -- The name of the new part
    -- Optional - Set *one* of these if you want to specify a Part to copy the values from.  Additional values override these settings 
    , @CopyFromPartID    INT         = NULL      -- set this value to the ID of a part to use as a template
    , @CopyFromPartName  VARCHAR(100)= NULL      -- set this value to the exact name of a part to use as a template                                                  
 
    -- Part Type must be specified if @CopyFromPart is not used
    , @PartTypeID           TINYINT = NULL      -- The type of part.  Valid Part Type IDs are:
                                                --   0 = Material           4 = Other
                                                --   1 = Labor              5 = Freight
                                                --   2 = Equipment          6 = Location
                                                --   3 = Outsource
 
    -- One of the two values is required to identify the category.  If you supply both, only @CategoryID is used
    , @CategoryID           INT         = NULL      -- The ID of the part category this part belongs under
    , @CategoryName         VARCHAR(100)= NULL      -- The name of the existing part category this part belongs under
 
    -- Optional General Settings
    , @Description          VARCHAR(1024)   = NULL  -- The description for this part.
    , @SKU                  VARCHAR(50) = NULL      -- The SKU for this part
    , @DefaultStationID     INT         = NULL      -- The default station this part is used in.
 
    , @UDFXML               VARCHAR(MAX)= NULL    -- The value for any UDFs set for this product.  Ignored if @CopyUDFsFromPartID is not null
 
    , @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  
 
    -- Optional Unit values
    , @UnitText             VARCHAR(25)     = NULL    -- The default unit for this part. The value must be from the _Unit table.  Defaults to 'Each'
    , @DisplayUnitText      VARCHAR(25)     = NULL      -- The display unit for this part. If not specified, the @UnitText is used.
    , @DisplayUnitsInUnit   DECIMAL(18,4)   = NULL      -- How many display units are in 1 standard unit.  Leave blank to use the default.   
 
    -- Optional Cost, Price and Consumption Settings
    , @UnitCost             DECIMAL(18,4)   = NULL      -- The number for the cost per unit.  
    , @UnitCostFormula      VARCHAR(1024)   = NULL      -- The formula for the cost per unit.  
 
    , @CostMultiplierFormula    VARCHAR(1024)   = NULL  -- The number or formula for the price multiplier.  Set to a Non-null to override the default multiplier
    , @PriceMultiplierFormula   VARCHAR(1024)   = NULL  -- The number or formula for the price.  Set to a non-null to use the price 
 
    , @ScrapFactor          DECIMAL(18,4)   = NULL      -- The scrap factor to add.  Set to a non-null value to enabled
    , @RoundingFactor       DECIMAL(18,4)   = NULL     -- The scrap rounding factor. Set to 1.0 for whole sheet, 0.5 for half-sheet, etc.  Defaults to 1
 
    -- Optional Accounting Settings
    , @UseCategoryDefaults  BIT         = NULL         -- Set this to zero to override the inventory and accounting options 
 
    , @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
 
    -- Optional Resell Product Information
    , @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
    , @PartID                INT     = NULL OUTPUT  -- New Part 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;
 
    DECLARE @UnitTypeID            TINYINT
            ,  @UnitID             TINYINT
            ,  @GUsInUnit          DECIMAL(18,4)
            ,  @DisplayUnitTypeID  TINYINT
            ,  @DisplayUnitID      TINYINT
            ,  @TransferUnitTypeID TINYINT
            ,  @TransferUnitID     TINYINT
            ,  @PartUnitConversionID    INT
            ,  @MultiplierType     TINYINT;     -- 0 for Default, 1 for Multiplier Formula, 2 for Price Formula
 
    DECLARE @InventoryID            INT     = NULL  -- New ID for the FIRST Warehouse detail Record (if inventory is enabled) 
            , @InventorySummaryID   INT     = NULL  -- New ID for the FIRST Summary Warehouse Record (if inventory is enabled)
            , @WarehouseCount       INT     = NULL  -- The number of active Warehouses
            , @WarehouseGroupCount  INT     = NULL  -- The number of active Warehouse Groups
 
    -- ----------------------------------------------------
    -- Lookup the correct keys
    -- ----------------------------------------------------
 
    IF (@CopyFromPartID IS NULL AND @CopyFromPartName IS NOT NULL)
        SET @CopyFromPartID = (SELECT ID FROM Part WHERE ItemName = @CopyFromPartName );
 
    IF (@CopyFromPartID IS NOT NULL)
        SELECT    @CategoryID              =  COALESCE( @CategoryID            , CategoryID            )
                , @PartTypeID              =  COALESCE( @PartTypeID            , PartType              )
                , @Description             =  COALESCE( @Description           , (CASE WHEN Description = ItemName THEN NULL ELSE Description END)  )
                , @SKU                     =  COALESCE( @SKU                   , (CASE WHEN Description = SKU THEN NULL ELSE SKU END)                   )
                , @DefaultStationID        =  COALESCE( @DefaultStationID      , StationID             )
                , @UDFXML                  =  COALESCE( @UDFXML                , CONVERT(VARCHAR(MAX), UDFXML) )
                , @UDFLayoutSettings       =  COALESCE( @UDFLayoutSettings     , CONVERT(VARCHAR(MAX), UDFLayoutSettings) )
                , @LocalTags               =  COALESCE( @LocalTags             , LocalTags             )
                , @UnitText                =  COALESCE( @UnitText              , (SELECT U.UnitText FROM [_Unit] U WHERE U.UnitID = Part.UnitID) )
                , @DisplayUnitText         =  COALESCE( @DisplayUnitText       , DisplayUnitText       )
                --, @DisplayUnitsInUnit      =  COALESCE( @DisplayUnitsInUnit    , DisplayUnitsInUnit    )
                , @UnitCost                =  COALESCE( @UnitCost              , UnitCost              )
                , @UnitCostFormula         =  COALESCE( @UnitCostFormula       , UnitCostFormula       )
                , @MultiplierType          =  CostMultiplierType
                , @CostMultiplierFormula   =  COALESCE( @CostMultiplierFormula , CostMultiplier        )
                , @PriceMultiplierFormula  =  COALESCE( @PriceMultiplierFormula, FixedCostMultiplier   )
                , @ScrapFactor             =  COALESCE( @ScrapFactor           , ScrapFactor           )
                , @RoundingFactor          =  COALESCE( @RoundingFactor        , RoundingFactor        )
                , @UseCategoryDefaults     =  COALESCE( @UseCategoryDefaults   , UseCategoryDefaults   )
                , @TrackCosts              =  COALESCE( @TrackCosts            , TrackCosts            )
                , @ExpenseAccountID        =  COALESCE( @ExpenseAccountID      , ExpenseAccountID      )
                , @TrackInventory          =  COALESCE( @TrackInventory        , TrackInventory        )
                , @AccrueCosts             =  COALESCE( @AccrueCosts           , AccrueCosts           )
                , @AccrualAccountID        =  COALESCE( @AccrualAccountID      , AssetAccountID        )
                , @ResellProductID         =  COALESCE( @ResellProductID       , ResellProductID       )
                , @ResellVariableID        =  COALESCE( @ResellVariableID      , ResellProductVariableID )
        FROM Part
        WHERE ID = @CopyFromPartID
 
    -- Else set default values if not passed in
    ELSE
        SELECT    @Description             =  COALESCE( @Description           , @PartName  )
                , @UDFXML                  =  COALESCE( @UDFXML                , '<UDFs/>' )
                , @UnitText                =  COALESCE( @UnitText              , 'Each' )
                , @ScrapFactor             =  COALESCE( @ScrapFactor           , 0.0    )
                , @RoundingFactor          =  COALESCE( @RoundingFactor        , 1.00   )
                , @UseCategoryDefaults     =  COALESCE( @UseCategoryDefaults   , 1   )
                , @TrackCosts              =  COALESCE( @TrackCosts            , 1   )
                , @TrackInventory          =  COALESCE( @TrackInventory        , 0   )
                , @AccrueCosts             =  COALESCE( @AccrueCosts           , 0   )
        ;
 
    IF (@CategoryID IS NULL)    
        SET @CategoryID = ( SELECT ID 
                            FROM PricingElement 
                            WHERE ElementName = @CategoryName 
                              AND ClassTypeID = 12035 );
 
    SELECT @UnitID = UnitID, @UnitTypeID = UnitType, @GUsInUnit = ConversionUnit
    FROM [_Unit] 
    WHERE (UnitText = @UnitText OR UnitAbbreviation = @UnitText);
 
    IF (COALESCE(@GUsInUnit, 0.0)=0.0)  SET @GUsInUnit = 1.0;
 
    IF (@DisplayUnitText = @UnitText)   SET @DisplayUnitText = NULL;
 
    IF (@DisplayUnitText IS NOT NULL)
        SELECT @DisplayUnitID = UnitID, @DisplayUnitTypeID = UnitType, 
               @DisplayUnitsInUnit = (CASE WHEN @DisplayUnitsInUnit IS NULL THEN ConversionUnit / @GUsInUnit ELSE @DisplayUnitsInUnit END)  
        FROM [_Unit] 
        WHERE (UnitText = @DisplayUnitText OR UnitAbbreviation = @DisplayUnitText);
 
    IF (@UseCategoryDefaults = 1)
        SELECT @TrackCosts      = COALESCE(DefaultTrackCosts, @TrackCosts, 1)
             , @ExpenseAccountID= COALESCE(ExpenseID, @ExpenseAccountID)
             , @TrackInventory  = COALESCE(DefaultTrackInventory, @TrackInventory, 0)
             , @AccrueCosts     = COALESCE(DefaultAccrueCosts, @AccrueCosts, 0)
             , @AccrualAccountID= COALESCE(DefaultAssetAccountID, @AccrualAccountID)
             , @ResellProductID = COALESCE(ResellProductID, @ResellProductID)
             , @ResellVariableID= COALESCE(ResellProductVariableID, @ResellVariableID)
             -- Copy these if NULL
             , @UDFLayoutSettings= COALESCE(@UDFLayoutSettings, UDFLayoutSettings)  
             , @LocalTags        = COALESCE(@LocalTags, LocalTags)  
        FROM PricingElement 
        WHERE ID = @CategoryID AND ClassTypeID = 12035
 
    ELSE IF (@UDFLayoutSettings IS NULL OR @LocalTags IS NULL)
        -- Copy these if NULL
        SELECT @UDFLayoutSettings= COALESCE(@UDFLayoutSettings, UDFLayoutSettings, '<PartLayoutTabs/>')  
             , @LocalTags        = COALESCE(@LocalTags, LocalTags)  
        FROM PricingElement 
        WHERE ID = @CategoryID AND ClassTypeID = 12035
    ;
 
    IF (@MultiplierType IS NULL)
        SET @MultiplierType = (CASE WHEN @CostMultiplierFormula IS NULL THEN 0
                                    WHEN @PriceMultiplierFormula IS NOT NULL THEN 2
                                    ELSE 1 
                               END);
 
 
    IF (@TrackInventory = 1)
    BEGIN
        SET @WarehouseCount      = (SELECT COUNT(*) FROM Warehouse WHERE ID>0 AND IsActive = 1 AND IsGroup = 0);
        SET @WarehouseGroupCount = (SELECT COUNT(*) FROM Warehouse WHERE ID>0 AND IsActive = 1 AND IsGroup = 1);
    END;
 
    -- -----------------------------------------------------------
    -- Validate the Input
    -- -----------------------------------------------------------
    IF (@CopyFromPartID IS NULL AND @CopyFromPartName IS NOT NULL) 
        SET @ErrorMessage = @ErrorMessage + '@CopyFromPartName "'+@CopyFromPartName+'" not found".; ';
 
    IF (@CopyFromPartID IS NOT NULL AND @CategoryID IS NULL) 
        SET @ErrorMessage = @ErrorMessage + '@CopyFromPartID '+CONVERT(INT, @CopyFromPartID)+' not found".; ';
 
    IF (@CategoryID IS NULL)
        SET @ErrorMessage = @ErrorMessage + '@CategoryID not set or found".; ';
 
    IF (@PartTypeID IS NULL) 
        SET @ErrorMessage = @ErrorMessage + '@PartTypeID not specified.; ';
 
    IF (@UnitID IS NULL) 
        SET @ErrorMessage = @ErrorMessage + '@UnitText not Found in _Unit table.; ';
 
    IF (@DisplayUnitID IS NULL AND @DisplayUnitText IS NOT NULL) 
        SET @ErrorMessage = @ErrorMessage + '@DisplayUnitText not Found in _Unit table.; ';
 
    IF (@PartTypeID != 0 AND @TrackInventory = 1) 
        SET @ErrorMessage = @ErrorMessage + 'You can only Track Inventory (@TrackInventory=1) on Material Parts (@PartType=0); ';
 
 
    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;    
 
 
    -- ----------------------------------------------------
    -- **** FOR NOW, WE AREN'T HANDLING INVENTORY *** 
    -- ----------------------------------------------------
    SET @TrackInventory = 0;
 
    -- ----------------------------------------------------
    -- Obtain new IDs using SQL Bridge
    -- ----------------------------------------------------
 
    IF (@PartID IS NULL )  SET @PartID = (SELECT dbo.csf_chapi_nextid( 12014, 1));
 
    IF (@DisplayUnitID IS NOT NULL AND @DisplayUnitID != @UnitID)
        SET @PartUnitConversionID = (SELECT dbo.csf_chapi_nextid( 12016, 1 ));
 
    IF (@TrackInventory =1)
    BEGIN
        SET @InventoryID        = (SELECT dbo.csf_chapi_nextid( 12200, @WarehouseCount ));
        SET @InventorySummaryID = (SELECT dbo.csf_chapi_nextid( 12200, @WarehouseGroupCount ));
    END;
 
    -- ----------------------------------------------------
    -- Create the Records
    -- ----------------------------------------------------
 
    BEGIN TRANSACTION
    BEGIN TRY
 
 
        INSERT INTO Part
                (ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive
                , CategoryID, CategoryStoreID, CategoryClassTypeID, ItemName, Description, HelpText, InternalNotes
                , BarCode, SKU, AssetAccountID, ExpenseAccountID, InventoryUnitType, PartType, TrackInventory, QuantityOnHand
                , QuantityReserved, QuantityAvailable, QuantityOnOrder, QuantityExpected, ReOrderPoint, ReOrderQuantity
                , YellowNotificationPoint, RedNotificationPoint, NotificationAddresses, NotifyOtherContacts, UnitID
                , RoundingOption, ScrapFactor, UnitCost, TrackCosts, UnitCostFormula, UseFixedCost, CostMultiplier
                , CostMultiplierType, FixedCostMultiplier, ShowOnProductionSchedule, InstancesAllowedType, InstancesAllowed
                , Vendor, VendorPartNumber, RoundingFactor, StationID, StationClassTypeID, GLDepartmentID, GLDepartmentClassTypeID
                , InventoryID, AccrueCosts, ExpenseAccountClassTypeID, AssetAccountClassTypeID, UseCategoryDefaults
                , ComputedCostAccountID, ComputedCostAccountClassTypeID, LastModifiedDate, UDFLayoutSettings, LocalTags
                , UDFXML, UseInvUnitsForDisplay, DisplayUnitID, DisplayUnitType, DisplayUnitText, DisplayConversionFormula
                , GLDepartmentIDOV, ExpenseAccountIDOV, AssetAccountIDOV, TransferUnitID, TransferUnitQuantity, TransferUnitText
                , ResellProductID, ResellProductIDOV, ResellProductVariableID)
        VALUES (
                  @PartID  -- <ID,  int, >
                , -1           -- ,<StoreID, int,>
                , 12014         -- ,<ClassTypeID, int,>
                , @ProcName  -- ,<ModifiedByUser, nvarchar(25),>
                , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),>
                , @DT    -- ,<ModifiedDate, datetime,>
                , 0            -- ,<SeqID, int,>
                , 0            -- ,<IsSystem, bit,>
                , 1            -- ,<IsActive, bit,>
                , @CategoryID    -- <CategoryID,  int, >
                , NULL    -- <CategoryStoreID,  int, >
                , 12035    -- <CategoryClassTypeID,  int, >
                , @PartName    -- <ItemName,  varchar(100), >
                , COALESCE(@Description, @PartName)    -- <Description,  varchar(max), >
                , NULL    -- <HelpText,  varchar(max), >
                , NULL    -- <InternalNotes,  varchar(max), >
                , NULL    -- <BarCode,  varchar(50), >
                , @SKU    -- <SKU,  varchar(50), >
                , @AccrualAccountID    -- <AssetAccountID,  int, >
                , @ExpenseAccountID    -- <ExpenseAccountID,  int, >
                , @UnitTypeID    -- <InventoryUnitType,  int, >
                , @PartTypeID    -- <PartType,  int, >   
                , @TrackInventory    -- <TrackInventory,  bit, >
                , NULL    -- <QuantityOnHand,  decimal(18, 4), >
                , NULL    -- <QuantityReserved,  decimal(18, 4), >
                , NULL    -- <QuantityAvailable,  decimal(18, 4), >
                , NULL    -- <QuantityOnOrder,  decimal(18, 4), >
                , NULL    -- <QuantityExpected,  decimal(18, 4), >
                , NULL    -- <ReOrderPoint,  decimal(18, 4), >
                , NULL    -- <ReOrderQuantity,  decimal(18, 4), >
                , NULL    -- <YellowNotificationPoint,  decimal(18, 4), >
                , NULL    -- <RedNotificationPoint,  decimal(18, 4), >
                , NULL    -- <NotificationAddresses,  varchar(max), >
                , NULL    -- <NotifyOtherContacts,  bit, >
                , @UnitID    -- <UnitID,  int, >
                , (CASE @RoundingFactor 
                        WHEN 1.0 THEN 0 
                        WHEN 0.5 THEN 1
                        WHEN 0.25 THEN 2
                        WHEN 0.20 THEN 3
                        WHEN 0.10 THEN 4
                        WHEN 0.01 THEN 5
                        WHEN 0.001 THEN 6
                        WHEN 0.0001 THEN 7
                        WHEN 0.125 THEN 8
                        WHEN 5.0 THEN 9
                        WHEN 10.0 THEN 10
                        WHEN 25.0 THEN 11
                        WHEN 50.0 THEN 12
                        WHEN 100.0 THEN 13 
                        WHEN 250.0 THEN 14 
                        WHEN 500.0 THEN 15
                        WHEN 1000.0 THEN 16 
                        ELSE 17 -- Custom
                        END )    -- <RoundingOption,  int, >
                , @ScrapFactor   -- <ScrapFactor,  decimal(18, 4), >
                , @UnitCost      -- <UnitCost,  decimal(18, 4), >
                , @TrackCosts   -- <TrackCosts,  bit, >
                , @UnitCostFormula    -- <UnitCostFormula,  varchar(max), >
                , ISNUMERIC(@UnitCost)    -- <UseFixedCost,  bit, >
                , @CostMultiplierFormula    -- <CostMultiplier,  varchar(max), >
                , @MultiplierType    -- <CostMultiplierType,  int, >
                , @PriceMultiplierFormula    -- <FixedCostMultiplier,  varchar(max), >
                , 0    -- <ShowOnProductionSchedule,  bit, >
                , 0    -- <InstancesAllowedType,  int, >
                , 1    -- <InstancesAllowed,  int, >
                , NULL    -- <Vendor,  varchar(40), >
                , NULL    -- <VendorPartNumber,  varchar(40), >
                , @RoundingFactor    -- <RoundingFactor,  decimal(18, 4), >
                , @DefaultStationID    -- <StationID,  int, >
                , 26100    -- <StationClassTypeID,  int, >
                , NULL    -- <GLDepartmentID,  int, >
                , NULL    -- <GLDepartmentClassTypeID,  int, >
                , NULL    -- <InventoryID,  int, >
                , @AccrueCosts    -- <AccrueCosts,  bit, >
                , 8001    -- <ExpenseAccountClassTypeID,  int, >
                , 8001    -- <AssetAccountClassTypeID,  int, >
                , @UseCategoryDefaults    -- <UseCategoryDefaults,  bit, >
                , NULL    -- <ComputedCostAccountID,  int, >
                , NULL    -- <ComputedCostAccountClassTypeID,  int, >
                , @DT    -- <LastModifiedDate,  smalldatetime, >
                , @UDFLayoutSettings    -- <UDFLayoutSettings,  varchar(max), >
                , @LocalTags    -- <LocalTags,  varchar(max), >
                , @UDFXML    -- <UDFXML,  xml, >
                , (CASE WHEN @DisplayUnitID IS NULL THEN 1 ELSE 0 END)    -- <UseInvUnitsForDisplay,  bit, >
                , COALESCE(@DisplayUnitID, @UnitID)    -- <DisplayUnitID,  int, >
                , COALESCE(@DisplayUnitTypeID, @UnitTypeID)    -- <DisplayUnitType,  int, >
                , COALESCE(@DisplayUnitText, @UnitText)    -- <DisplayUnitText,  varchar(25), >
                , '1 / '+CONVERT(VARCHAR(18), @DisplayUnitsInUnit)  -- <DisplayConversionFormula,  varchar(max), >
                , NULL    -- <GLDepartmentIDOV,  bit, >
                , NULL    -- <ExpenseAccountIDOV,  bit, >
                , NULL    -- <AssetAccountIDOV,  bit, >
                , @UnitID    -- <TransferUnitID,  int, >
                , 1.00    -- <TransferUnitQuantity,  decimal(18, 4), >
                , @UnitText    -- <TransferUnitText,  varchar(50), >
                , @ResellProductID    -- <ResellProductID,  int, >
                , (CASE WHEN @ResellProductID IS NULL THEN 0 ELSE 1 END)    -- <ResellProductIDOV,  bit, >
                , @ResellVariableID    -- <ResellProductVariableID,  int, >
        );
 
        -- See if we have alternate display units
        IF (@PartUnitConversionID IS NOT NULL)
 
            INSERT INTO PartInventoryConversion
                    (ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive
                    , PartID, IsAutomatic, IsDefault, ConversionFormula, ConsumptionFormula, InclusionFormula, UnitID
                    , UnitType)
            VALUES(
                    @PartUnitConversionID  -- <ID,  int, >
                    , -1           -- ,<StoreID, int,>
                    , 12016         -- ,<ClassTypeID, int,>
                    , @ProcName  -- ,<ModifiedByUser, nvarchar(25),>
                    , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),>
                    , @DT    -- ,<ModifiedDate, datetime,>
                    , 0            -- ,<SeqID, int,>
                    , 0            -- ,<IsSystem, bit,>
                    , 1            -- ,<IsActive, bit,>
                    , @PartID    -- <PartID,  int, >
                    , 0    -- <IsAutomatic,  bit, >
                    , 0    -- <IsDefault,  bit, >
                    , '1 / '+CONVERT(VARCHAR(18), @DisplayUnitsInUnit)  -- <ConversionFormula,  varchar(max), >
                    , '1'    -- <ConsumptionFormula,  varchar(max), >
                    , 'Always'    -- <InclusionFormula,  varchar(max), >
                    , @DisplayUnitID    -- <UnitID,  int, >
                    , @DisplayUnitTypeID    -- <UnitType,  int, >
            );
 
        -- ------------------------------------------
        -- Iterate through each Warehouse and Warehouse Groups
        -- ------------------------------------------
 
        -- IF (@TrackInventory = 1)
        -- BEGIN        
 
            -- INSERT INTO Inventory
            --         (ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive
            --         , ItemName, PartID, AverageCost, QuantityAvailable, QuantityBilled, QuantityExpected, QuantityOnHand
            --         , QuantityOnOrder, QuantityReceivedOnly, QuantityReserved, AssetAccountID, AssetAccountIDOV, UnitType
            --         , UnitID, YellowNotificationPoint, RedNotificationPoint, ReorderPoint, ReOrderQuantity, AssetAccountClassTypeID
            --         , UseCategoryDefaults, WarehouseID, DivisionID, IsGroup, IsDivisionSummary, GroupID, AlertLevel, Location)
            -- VALUES(
            --             -- <ID,  int, >
            --         , -1           -- ,<StoreID, int,>
            --         , 12200         -- ,<ClassTypeID, int,>
            --         , @ProcName  -- ,<ModifiedByUser, nvarchar(25),>
            --         , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),>
            --         , @DT    -- ,<ModifiedDate, datetime,>
            --         , 0            -- ,<SeqID, int,>
            --         , 0            -- ,<IsSystem, bit,>
            --         , 1            -- ,<IsActive, bit,>
            --         ,     -- <ItemName,  varchar(50), >
            --         ,     -- <PartID,  int, >
            --         ,     -- <AverageCost,  decimal(18, 4), >
            --         ,     -- <QuantityAvailable,  decimal(18, 4), >
            --         ,     -- <QuantityBilled,  decimal(18, 4), >
            --         ,     -- <QuantityExpected,  decimal(18, 4), >
            --         ,     -- <QuantityOnHand,  decimal(18, 4), >
            --         ,     -- <QuantityOnOrder,  decimal(18, 4), >
            --         ,     -- <QuantityReceivedOnly,  decimal(18, 4), >
            --         ,     -- <QuantityReserved,  decimal(18, 4), >
            --         ,     -- <AssetAccountID,  int, >
            --         ,     -- <AssetAccountIDOV,  bit, >
            --         ,     -- <UnitType,  int, >
            --         ,     -- <UnitID,  int, >
            --         ,     -- <YellowNotificationPoint,  decimal(18, 4), >
            --         ,     -- <RedNotificationPoint,  decimal(18, 4), >
            --         ,     -- <ReorderPoint,  decimal(18, 4), >
            --         ,     -- <ReOrderQuantity,  decimal(18, 4), >
            --         ,     -- <AssetAccountClassTypeID,  int, >
            --         ,     -- <UseCategoryDefaults,  bit, >
            --         ,     -- <WarehouseID,  int, >
            --         ,     -- <DivisionID,  int, >
            --         ,     -- <IsGroup,  bit, >
            --         ,     -- <IsDivisionSummary,  bit, >
            --         ,     -- <GroupID,  int, >
            --         ,     -- <AlertLevel,  varchar(150), >
            --         ,     -- <Location,  varchar(255), >
            -- );
 
        -- END;
 
        -- Now commit the Transaction
        COMMIT TRANSACTION
    END TRY
 
    BEGIN CATCH
        ROLLBACK TRANSACTION;
 
        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 @PartID, 12014, -1;
        EXEC dbo.csf_chapi_refresh @CategoryID, 12035, -1;
    END;
 
    -- ----------------------------------------------------------
    -- Return the New Part ID
    -- ----------------------------------------------------------
    SELECT @PartID, 12035
END;

Source

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

You could leave a comment if you were logged in.