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
- 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+