SQL Stored Procedure - Import Line Item
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 add a line item to an existing order or estimate in Control.
You must identify the order or estimate with one (and only one) of the following:
- @OrderNumber - The order number
- @EstimateNumber - The estimate number
- @THID - The TransHeader.ID database key field for the record.
For the line item, you must supply one (and only one) of the following:
- @ProductName - The exact name of the product (not case sensitive).
- @ProductID - The Product.ID database key field for the product.
You must also supply all of the following required information:
- @Quantity - The number of items for this line item
- @RecomputeOnSave - Whether you want the order recomputed when you save this line item. Set this to 1 to recompute, or 0 to not recompute. If you are going to make other changes to the order, it is recommended you only do one recompute at the end.
The following optional values may also be supplied. If you leave a value out, it generally will default to the value Control would set if you did not change it.
- @RefreshOnSave - Set this 1 to send a refresh message to Control to re-load the order. Set it to 0 to not send this. If you are recomputing the order, this is done automatically and may be ignored. If you are going to make other changes to the order, it is recommended you only do one refresh at the end.
- @AddJournal - Set this to 1 to create a journal entry on the order showing the line item was added.
- @EmployeeID - The Employee.ID of the user making the change. This is used in the Journal.
- @BasePrice - The price of the line item before discounts and taxes.
- @BasePriceOV - Set this to 1 if the price is overridden and should not be recalculated. Set this to 0 if the price should be recalculated when the order is recomputed. Defaults to 1 (overridden).
- @Discount - The discount amount for this line.
- @DiscountRate - The percentage discount rate for this line, entered as a decimal (e.g., enter 0.10 for 10%). Do not enter anything here unless you want the discount to recalculate as the price changes.
- @Tax - The tax amount for this line (in dollars, not percent). The tax amount is always recalculated on recompute.
- @Description - The description of the line item purchase.
- @ProductionNotes - The internal or production notes for this line item.
- @StationID - The production Station.ID this line item should start in. Defaults to the products default Station if left blank.
- @IsComplete - Set this to 1 if the line item is complete in production, otherwise set this to zero. Defaults to 0.
- @AssignedToID - The Employee.ID of the person currently assigned to produce the order.
- @GLAccountID - The income Account for this line. If left blank, this defaults to the products default income account.
- @GLDepartmentID - The accounting GL Department for the income and expense for this line item.
- @ImageHeight - The Image Height (in input units) for the line item.
- @ImageWidth - The Image Width (in input units) for the line item.
- @ImportDictionary - (future) An representation of other variable to be saved for the product.
The Stored Procedure returns the ID and ClassTypeID of the new line item.
Notes:
- In general, don't set values you want to use the default to. For instance, if you set the @TaxClassID to the value the customer is set to, this will force it to be overridden and it won't change even if the customer's tax class is updated. Just leave these columns out of the call if you want to use the default value.
The stored procedure requires sql_bridge in order to obtain IDs for the inserted records. sql_bridge is a collection of SQL Server stored prodedures and functions and external modules that can be called to safely insert or update data into the Control database and notify the CHAPI service that data has been updated. The CHAPI service is a controller program that will then notify the SSLIP and all copies of Control about the changes.
Example Usage
This inserts two line items in order #1181. The order is recalculated after the 2nd line item.
-- ============================================= -- Author: Cyrious Sofware -- Create date: May-2016 -- Description: This stored procedure imports a line item into an existing order or estimate. -- Many of the parameters are option, but if not supplied will used -- The default behavior. -- -- Returns: New TransDetailID, TransDetailClassTypeID -- ============================================= ALTER PROCEDURE csp_ImportLineItem -- You must supply one (and only one) of the following @THID INT = NULL, @OrderNumber INT = NULL, @EstimateNumber INT = NULL, -- You must supply one (and only one) of the following @ProductName VARCHAR(255) = NULL, -- Product Name. Must match exactly. @ProductID INT = NULL, -- Product.ID You must supply either the ProductName of the ProductID. -- You must supply all of the following @Quantity DECIMAL(18,4), -- the quantity of items for ths line @RecomputeOnSave BIT, -- set to 1 to have the order to recompute and update on Save. 0 to do it manually afterwards. -- Optional values @RefreshOnSave BIT = 1, -- 0 = Don't Refresh the order on Save, 1 = Do Refresh @AddJournal BIT = 1, -- 0 = Don't save a journal recording the new line, 1 = Do Save it @EmployeeID INT = NULL, -- The Employee ID making the save. Used in the Journal if provided. @BasePrice DECIMAL(18,4) = NULL, -- the extended base price of the line (pre-discount and taxes) @BasePriceOV BIT = 1, -- 1 if the price should not be recalculated on edit, 0 if the price can be racalculated on edit. @Discount DECIMAL(18,4) = NULL, -- the discount for this line. Always put the dollar value of the discount here. @DiscountRate DECIMAL(18,4) = NULL, -- the discount rate for this line. Only use this if the discount should be adjusted as a percentage. @Tax DECIMAL(18,4) = NULL, -- the amount of taxes for this line item, if applicable. @Description VARCHAR(4096) = NULL, -- any line item notes for this product @ProductionNotes VARCHAR(4096) = NULL, -- any production notes for this product. @StationID INT = NULL, -- the line item station @IsComplete BIT = 0, -- 1 = Item is Complete, 0 = not complete. Only used for WIP orders. @AssignedToID INT = NULL, -- Enter the employee this is assigned to. Leave blank for no assignment. @GLAccountID INT = NULL, -- the Income Account ID for this product. Leave blank to use the default. @GLDepartmentID INT = NULL, -- the GL Department for this order, if desired. -- set variables informtion here. -- This data should be in INI format (variablename=value) with paragraph delimiters -- All strings must be delimited with single quotes -- Multiple line strings are not supported @ImageHeight DECIMAL(18,4) = NULL, -- the height of the product. Leave blank to use the default @ImageWidth DECIMAL(18,4) = NULL, -- the width of the proudct. Leave blank to use the default @ImportDictionary VARCHAR(MAX) = NULL, -- addtional variables can be set using the import dictionary and product variable mapping -- Some OUTPUT Parameters in case the caller wants any of these value back @TDID INT = NULL OUTPUT AS BEGIN -- -- Step 0. Validate the Input -- DECLARE @Logs TABLE( ID INT, ClassTypeID INT, ParentID INT, IsError BIT, Summary VARCHAR(255), Detail VARCHAR(2000) ); DECLARE @ValidationError VARCHAR(2048) = ''; IF (IsNumeric(@THID) +IsNumeric(@OrderNumber) + IsNumeric(@EstimateNumber) <> 1) SET @ValidationError = @ValidationError + 'You must supply one and only one of these inputs: @OrderNumber, @EstimateNumber, and @THID; '; IF ( ( (CASE WHEN @ProductID IS NULL THEN 0 ELSE 1 END) + (CASE WHEN @ProductName IS NULL THEN 0 ELSE 1 END) ) <> 1) SET @ValidationError = @ValidationError + 'You must supply one and only one of these inputs: @ProductName and @ProductID; '; IF (@THID IS NULL) BEGIN IF (@OrderNumber IS NOT NULL) BEGIN SET @THID = (SELECT ID FROM TransHeader WHERE (OrderNumber = @OrderNumber AND TransactionType IN (1,6)) ); IF (@THID IS NULL) SET @ValidationError = @ValidationError + 'Order Number '+CONVERT(VARCHAR(12), @OrderNumber)+' does not exist; '; END; IF (@EstimateNumber IS NOT NULL) BEGIN SET @THID = (SELECT ID FROM TransHeader WHERE (EstimateNumber = @EstimateNumber AND TransactionType = 2) ); IF (@THID IS NULL) SET @ValidationError = @ValidationError + 'Estimate Number '+CONVERT(VARCHAR(12), @EstimateNumber)+' does not exist; '; END; END ELSE BEGIN IF NOT EXISTS( SELECT 1 FROM TransHeader WHERE ID = @THID AND TransactionType IN (1,2,6) ) SET @ValidationError = @ValidationError + 'TransHeader.ID = '+CONVERT(VARCHAR(12), @THID)+' does not exist or is not an order or estimate; '; END; IF (@ValidationError <> '') BEGIN INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail) VALUES( NULL, 10100, @THID, 1, 'Line Item Not Imported due to Validation Errors.', 'Validation Errors: '+@ValidationError ) SELECT * FROM @Logs; RETURN; END; -- -- Step 1. Declare some variable and look up some information -- -- Define some fixed values for reference DECLARE @DT SMALLDATETIME = GetDate(); DECLARE @ComputerName VARCHAR(25) = @@ServerName; DECLARE @NewLine CHAR(2) = CHAR(10)+CHAR(13); -- Determine the Order or Estimate -- Order Related Variables DECLARE @TransNumber INT ; DECLARE @TransactionType TINYINT ; DECLARE @OrderDueDate SMALLDATETIME ; DECLARE @OrderProofDate SMALLDATETIME ; DECLARE @DivisionID INT ; DECLARE @AccountID INT ; DECLARE @ContactID INT ; SELECT @THID = ID, @EstimateNumber = CASE WHEN TransactionType = 2 THEN EstimateNumber ELSE NULL END, @OrderNumber = CASE WHEN TransactionType IN (1,6) THEN OrderNumber ELSE NULL END, @TransNumber = CASE WHEN TransactionType = 2 THEN EstimateNumber ELSE OrderNumber END, @TransactionType= TransactionType, @OrderDueDate = DueDate, @OrderProofDate = ProofDate, @DivisionID = DivisionID, @AccountID = AccountID, @ContactID = ContactID FROM TransHeader WHERE ID = @THID; -- Variation Information DECLARE @TVID INT = (SELECT Top(1) ID FROM TransVariation WHERE ParentID = @THID ORDER BY SortIndex ASC); -- Journal Related Information DECLARE @JournalID INT = (SELECT dbo.csf_chapi_nextid( 20500, 1)); -- Order Activity IF (@EmployeeID IS NULL) SET @EmployeeID = 10; -- House Account -- Company Information DECLARE @PricingPlanID INT = COALESCE((SELECT PricingPlanTypeID FROM Account WHERE ID = @AccountID), 10); -- Line Item Information SET @TDID = (SELECT dbo.csf_chapi_nextid( 10100, 1)); DECLARE @GLAccountOV BIT = (CASE WHEN @GLAccountID IS NULL THEN 0 ELSE 1 END); DECLARE @DiscountRateOV BIT = (CASE WHEN @DiscountRate IS NULL THEN 0 ELSE 1 END); DECLARE @BaseProductID INT; DECLARE @IsDerivedProduct BIT; SELECT Top(1) @ProductID = COALESCE( @ProductID, ID), @ProductName = COALESCE( @ProductName, ItemName ), @GLAccountID = COALESCE( @GLAccountID, ( SELECT top(1) PP.AccountCodeID FROM PricingPlan PP WHERE PP.GoodsItemID = P.ID AND PP.PricingPlanTypeID IN (10, @PricingPlanID) ORDER BY PricingPlanTypeID DESC ) ), @StationID = COALESCE( @StationID, StationID ), @BaseProductID = BaseProductID, @IsDerivedProduct = (CASE WHEN BaseProductID IS NULL THEN 0 ELSE 1 END) FROM CustomerGoodsItem P WHERE (ItemName = @ProductName OR ID = @ProductID); SET @Discount = COALESCE( @Discount, @BasePrice * @DiscountRate ); DECLARE @SubTotalPrice DECIMAL(18,4) = (@BasePrice - COALESCE(@Discount, 0.0)); DECLARE @TotalPrice DECIMAL(18,4) = (@SubTotalPrice + COALESCE(@Tax, 0.0)); DECLARE @LineItemIndex INT = (SELECT COUNT(*) FROM TransDetail WHERE ParentID = @THID); DECLARE @LineItemNumber CHAR(3) = CONVERT(CHAR(3), @LineItemIndex+1); -- Create the Variable XML Record DECLARE @VariableXML XML; -- encode variables for XML (or saving as XML will fail) SET @Description = (SELECT @Description FOR XML PATH('')); SET @ProductionNotes = (SELECT @ProductionNotes FOR XML PATH('')); SET @VariableXML = '<Parameters>' + '<Parameter>' + '<VariableName>Quantity</VariableName>' + '<ValueAsString>'+CONVERT(VARCHAR(16), @Quantity)+'</ValueAsString>' + '<Overridden>1</Overridden>' + '</Parameter>' + '<Parameter>' + '<VariableName>Description</VariableName>' + '<ValueAsString>' + @Description +'</ValueAsString>' + '<Overridden>1</Overridden>' + '</Parameter>' + CASE WHEN @ImageHeight IS NOT NULL THEN '<Parameter>' + '<VariableName>ImageHeight</VariableName>' + '<ValueAsString>'+CONVERT(VARCHAR(19),@ImageHeight)+'</ValueAsString>' + '<Overridden>1</Overridden>' + '</Parameter>' ELSE '' END + CASE WHEN @ImageWidth IS NOT NULL THEN '<Parameter>' + '<VariableName>ImageWidth</VariableName>' + '<ValueAsString>'+CONVERT(VARCHAR(19),@ImageWidth)+'</ValueAsString>' + '<Overridden>1</Overridden>' + '</Parameter>' ELSE '' END + CASE WHEN @ProductionNotes IS NOT NULL THEN '<Parameter>' + '<VariableName>InternalNotes</VariableName>' + '<ValueAsString>' + @ProductionNotes + '</ValueAsString>' + '<Overridden>1</Overridden>' + '</Parameter>' ELSE '' END + '</Parameters>'; -- -- Step 2. Create the Records -- BEGIN TRANSACTION BEGIN TRY -- Update TransHeader Sequence ID UPDATE TransHeader SET SeqID = SeqID + 1, ModifiedDate = @DT, ModifiedByUser = 'SQLBridge' WHERE ID = @THID ; -- Insert TransDetail INSERT INTO [dbo].[TransDetail] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] , [TransactionType] , [ParentID], [ParentClassTypeID] , [TransHeaderID] , [TransHeaderClassTypeID] , [TransHeaderTransNumber] , [GoodsItemID] , [GoodsItemClassTypeID] , [GoodsItemCode] , [PricingLevel] , [PricingLevelOverridden] , [DiscountLevel] , [DiscountLevelOverridden] , [Description] , [Quantity] , [LineItemIndex] , [LineItemNumber] , [HTMLShortFormat] , [HTMLLongFormat] , [BasePrice] , [BaseCalcPrice] , [BaseOverriddenPrice] , [BaseIsOverridden] , [DiscountPrice] , [DiscountCalcPrice] , [DiscountOverriddenPrice] , [DiscountIsOverridden] , [ModifierPrice] , [ModifierCalcPrice] , [ModifierOverriddenPrice] , [ModifierIsOverridden] , [RawSubTotalPrice] , [RawSubTotalCalcPrice] , [RawSubTotalOverriddenPrice] , [RawSubTotalIsOverridden] , [RoundingPrice] , [RoundingCalcPrice] , [RoundingOverriddenPrice] , [RoundingIsOverridden] , [SubTotalPrice] , [SubTotalCalcPrice] , [SubTotalOverriddenPrice] , [SubTotalIsOverridden] , [TaxesPrice] , [TaxesCalcPrice] , [TaxesOverriddenPrice] , [TaxesIsOverridden] , [TotalPrice] , [TotalCalcPrice] , [TotalOverriddenPrice] , [TotalIsOverridden] , [MeAndSonsBasePrice] , [MeAndSonsDiscountPrice] , [MeAndSonsModifierPrice] , [MeAndSonsRawSubTotalPrice] , [MeAndSonsRoundingPrice] , [MeAndSonsSubTotalPrice] , [MeAndSonsTaxesPrice] , [MeAndSonsTotalPrice] , [InternalNotes] , [UnitParamName] , [UnitParamValue] , [VariationID] , [ImageParamID] , [ImageParamStoreID] , [ImageParamClassTypeID] , [StageID] , [StageStoreID] , [StageClassTypeID] , [SaleAccountID] , [SaleAccountStoreID] , [SaleAccountClassTypeID] , [SaleAccountOverridden] , [ChildItemCount] , [ParameterStr] , [ModifierStr] , [PartStr] , [VarGridParam1ID] , [VarGridParam2ID] , [VarGridValues1] , [VarGridValues2] , [VarGridParam1OV] , [VarGridParam2OV] , [TaxItems] , [IsComplete] , [AssignedToID] , [TicketItemNumber] , [ShippingCompanyID] , [ShippingCompanyOV] , [ShippingContactID] , [ShippingContactOV] , [ShippingAddressID] , [ShippingAddressLinkID] , [ShippingAddressOV] , [ShippingTaxClassID] , [ShippingTaxClassOV] , [TotMaterialCost] , [TotLaborCost] , [TotEquipmentCost] , [TotOutSourceCost] , [TotOtherCost] , [TotPartCost] , [HasWorkAssignment] , [WorkAssignmentID] , [WorkAssignmentStoreID] , [WorkAssignmentClassTypeID] , [TotFreightCost] , [PriorityID] , [PriorityStoreID] , [PriorityClassTypeID] , [AssignedToClassTypeID] , [ProofApproved] , [ProofApprovedDate] , [ProofApprovedByID] , [PercentComplete] , [PercentCompleteOV] , [TermID] , [ContractPeriodID] , [ContractStartDate] , [ContractEndDate] , [ShipFromAddressID] , [ShipFromAddressLinkID] , [ShipFromAddressOV] , [IsValidTax] , [IsDirty] , [TaxNumber] , [ShipFromID] , [ShipFromClassTypeID] , [AssemblyIncluded] , [AssemblyIncludedOV] , [AssemblyLocked] , [AssemblyLinkID] , [AssemblyLinkClassTypeID] , [OverriddenAssemblies] , [StationID] , [StationClassTypeID] , [GLDepartmentID] , [GLDepartmentIDOV] , [TaxablePrice] , [MeAndSonsTaxablePrice] , [StationActivityID] , [StationActivityClassTypeID] , [MaterialExpenseID] , [LaborExpenseID] , [EquipmentExpenseID] , [OutsourceExpenseID] , [OtherExpenseID] , [FreightExpenseID] , [LocationExpenseID] , [UseParentExpenseAccounts] , [WarehouseID] , [WarehouseIDOV] , [RanReturnSQLFuncStatements] , [QuantityShipped] , [DueDate] , [DueDateOV] , [ShipFromCustomerID] , [ShipFromCustomerClassTypeID] , [ShowMultipleQuantityGrid] , [MultipleQuantityGridXML] , [OldDescription] , [EstimatingWarehouseID] , [EstimatingWarehouseIDOV] , [ProductionDivisionID] , [ProductionDivisionIDOverridden] , [ProofDate], [ProofDateOV], [CalcTaxablePrice], [TaxInfo], [UsesExternalCFL], [IsPricingLocked], [BaseProductID], [ImportDictionary] , [ApplyDPChanges] ) VALUES ( @TDID -- <ID, int,> , -1 -- ,<StoreID, int,> , 10100 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @@ServerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @TransactionType -- <TransactionType, int,> , @THID -- <ParentID, int,> , 10000 -- <ParentClassTypeID, int,> , @THID -- <TransHeaderID, int,> , 10000 -- <TransHeaderClassTypeID, int,> , @OrderNumber -- <TransHeaderTransNumber, int,> , @ProductID -- <GoodsItemID, int,> , (CASE WHEN @IsDerivedProduct = 1 THEN 12005 ELSE 12000 END) -- <GoodsItemClassTypeID, int,> , @ProductName -- <GoodsItemCode, nvarchar(35),> , 1.0 -- <PricingLevel, decimal(18,4),> , 0 -- <PricingLevelOverridden, bit,> , @DiscountRate -- <DiscountLevel, decimal(18,4),> , @DiscountRateOV -- <DiscountLevelOverridden, bit,> , @Description -- <Description, varchar(max),> , @Quantity -- <Quantity, decimal(18,4),> , @LineItemIndex -- <LineItemIndex, int,> , @LineItemNumber -- <LineItemNumber, varchar(50),> , NULL -- <HTMLShortFormat, varchar(max),> , NULL -- <HTMLLongFormat, varchar(max),> , @BasePrice -- <BasePrice, decimal(18,4),> , @BasePrice -- <BaseCalcPrice, decimal(18,4),> , @BasePrice -- <BaseOverriddenPrice, decimal(18,4),> , @BasePriceOV -- <BaseIsOverridden, bit,> , @Discount -- <DiscountPrice, decimal(18,4),> , @Discount -- <DiscountCalcPrice, decimal(18,4),> , @Discount -- <DiscountOverriddenPrice, decimal(18,4),> , (CASE WHEN @Discount IS NULL THEN 0 ELSE 1 END ) -- <DiscountIsOverridden, bit,> , 0.0 -- <ModifierPrice, decimal(18,4),> , 0.0 -- <ModifierCalcPrice, decimal(18,4),> , 0.0 -- <ModifierOverriddenPrice, decimal(18,4),> , 0 -- <ModifierIsOverridden, bit,> , @SubTotalPrice -- <RawSubTotalPrice, decimal(18,4),> , @SubTotalPrice -- <RawSubTotalCalcPrice, decimal(18,4),> , @SubTotalPrice -- <RawSubTotalOverriddenPrice, decimal(18,4),> , 0 -- <RawSubTotalIsOverridden, bit,> , 0.0 -- <RoundingPrice, decimal(18,4),> , 0.0 -- <RoundingCalcPrice, decimal(18,4),> , 0.0 -- <RoundingOverriddenPrice, decimal(18,4),> , 0 -- <RoundingIsOverridden, bit,> , @SubTotalPrice -- <SubTotalPrice, decimal(18,4),> , @SubTotalPrice -- <SubTotalCalcPrice, decimal(18,4),> , @SubTotalPrice -- <SubTotalOverriddenPrice, decimal(18,4),> , 0 -- <SubTotalIsOverridden, bit,> , @Tax -- <TaxesPrice, decimal(18,4),> , @Tax -- <TaxesCalcPrice, decimal(18,4),> , @Tax -- <TaxesOverriddenPrice, decimal(18,4),> , 0 -- <TaxesIsOverridden, bit,> , @TotalPrice -- <TotalPrice, decimal(18,4),> , @TotalPrice -- <TotalCalcPrice, decimal(18,4),> , @TotalPrice -- <TotalOverriddenPrice, decimal(18,4),> , 0 -- <TotalIsOverridden, bit,> , @BasePrice -- <MeAndSonsBasePrice, decimal(18,4),> , @Discount -- <MeAndSonsDiscountPrice, decimal(18,4),> , 0.0 -- <MeAndSonsModifierPrice, decimal(18,4),> , @SubTotalPrice -- <MeAndSonsRawSubTotalPrice, decimal(18,4),> , 0.0 -- <MeAndSonsRoundingPrice, decimal(18,4),> , @SubTotalPrice -- <MeAndSonsSubTotalPrice, decimal(18,4),> , @Tax -- <MeAndSonsTaxesPrice, decimal(18,4),> , @TotalPrice -- <MeAndSonsTotalPrice, decimal(18,4),> , @ProductionNotes -- <InternalNotes, varchar(max),> , 'Quantity' -- <UnitParamName, varchar(25),> , @Quantity -- <UnitParamValue, decimal(18,4),> , @TVID -- <VariationID, int,> , NULL -- <ImageParamID, int,> , NULL -- <ImageParamStoreID, int,> , NULL -- <ImageParamClassTypeID, int,> , NULL -- <StageID, int,> , NULL -- <StageStoreID, int,> , NULL -- <StageClassTypeID, int,> , @GLAccountID -- <SaleAccountID, int,> , NULL -- <SaleAccountStoreID, int,> , 8001 -- <SaleAccountClassTypeID, int,> , @GLAccountOV -- <SaleAccountOverridden, bit,> , 0 -- <ChildItemCount, int,> , CONVERT(VARCHAR(MAX),@VariableXML) -- <ParameterStr, xml,> , NULL -- <ModifierStr, varchar(max),> , NULL -- <PartStr, varchar(max),> , NULL -- <VarGridParam1ID, int,> , NULL -- <VarGridParam2ID, int,> , NULL -- <VarGridValues1, varchar(max),> , NULL -- <VarGridValues2, varchar(max),> , NULL -- <VarGridParam1OV, bit,> , NULL -- <VarGridParam2OV, bit,> , NULL -- <TaxItems, varchar(max),> , 0 -- <IsComplete, bit,> , @AssignedToID -- <AssignedToID, int,> , (COALESCE(@OrderNumber, @EstimateNumber)*1000 + @LineItemIndex + 1) -- <TicketItemNumber, int,> , @AccountID -- <ShippingCompanyID, int,> , 0 -- <ShippingCompanyOV, bit,> , @ContactID -- <ShippingContactID, int,> , 0 -- <ShippingContactOV, bit,> , NULL -- <ShippingAddressID, int,> , NULL -- <ShippingAddressLinkID, int,> , 0 -- <ShippingAddressOV, bit,> , NULL -- <ShippingTaxClassID, int,> , 0 -- <ShippingTaxClassOV, bit,> , 0.0 -- <TotMaterialCost, decimal(18,4),> , 0.0 -- <TotLaborCost, decimal(18,4),> , 0.0 -- <TotEquipmentCost, decimal(18,4),> , 0.0 -- <TotOutSourceCost, decimal(18,4),> , 0.0 -- <TotOtherCost, decimal(18,4),> , 0.0 -- <TotPartCost, decimal(18,4),> , 0 -- <HasWorkAssignment, bit,> , NULL -- <WorkAssignmentID, int,> , NULL -- <WorkAssignmentStoreID, int,> , NULL -- <WorkAssignmentClassTypeID, int,> , 0.0 -- <TotFreightCost, decimal(18,4),> , NULL -- <PriorityID, int,> , NULL -- <PriorityStoreID, int,> , NULL -- <PriorityClassTypeID, int,> , 3500 -- <AssignedToClassTypeID, int,> , 0 -- <ProofApproved, bit,> , NULL -- <ProofApprovedDate, smalldatetime,> , NULL -- <ProofApprovedByID, int,> , 0.0 -- <PercentComplete, decimal(18,4),> , 0 -- <PercentCompleteOV, bit,> , NULL -- <TermID, int,> , NULL -- <ContractPeriodID, int,> , NULL -- <ContractStartDate, smalldatetime,> , NULL -- <ContractEndDate, smalldatetime,> , NULL -- <ShipFromAddressID, int,> , NULL -- <ShipFromAddressLinkID, int,> , 0 -- <ShipFromAddressOV, bit,> , 1 -- <IsValidTax, bit,> , 0 -- <IsDirty, bit,> , NULL -- <TaxNumber, varchar(25),> , NULL -- <ShipFromID, int,> , NULL -- <ShipFromClassTypeID, int,> , 0 -- <AssemblyIncluded, bit,> , 0 -- <AssemblyIncludedOV, bit,> , 0 -- <AssemblyLocked, bit,> , NULL -- <AssemblyLinkID, int,> , NULL -- <AssemblyLinkClassTypeID, int,> , NULL -- <OverriddenAssemblies, varchar(max),> , @StationID -- <StationID, int,> , 26100 -- <StationClassTypeID, int,> , @GLDepartmentID -- <GLDepartmentID, int,> , 0 -- <GLDepartmentIDOV, bit,> , @SubTotalPrice -- <TaxablePrice, decimal(18,4),> , @SubTotalPrice -- <MeAndSonsTaxablePrice, decimal(18,4),> , NULL -- <StationActivityID, int,> , NULL -- <StationActivityClassTypeID, int,> , NULL -- <MaterialExpenseID, int,> , NULL -- <LaborExpenseID, int,> , NULL -- <EquipmentExpenseID, int,> , NULL -- <OutsourceExpenseID, int,> , NULL -- <OtherExpenseID, int,> , NULL -- <FreightExpenseID, int,> , NULL -- <LocationExpenseID, int,> , 0 -- <UseParentExpenseAccounts, bit,> , NULL -- <WarehouseID, int,> , 0 -- <WarehouseIDOV, bit,> , NULL -- <RanReturnSQLFuncStatements, varchar(max),> , 0.0 -- <QuantityShipped, decimal(18,4),> , @OrderDueDate -- <DueDate, smalldatetime,> , 0 -- <DueDateOV, bit,> , NULL -- <ShipFromCustomerID, int,> , NULL -- <ShipFromCustomerClassTypeID, int,> , 0 -- <ShowMultipleQuantityGrid, bit,> , NULL -- <MultipleQuantityGridXML, xml,> , NULL -- <OldDescription, varchar(50),> , NULL -- <EstimatingWarehouseID, int,> , 0 -- <EstimatingWarehouseIDOV, bit,> , @DivisionID -- <ProductionDivisionID, int,> , 0 -- <ProductionDivisionIDOverridden, bit,> , @OrderProofDate -- <ProofDate, smalldatetime,> , 0 -- <ProofDateOV, bit,> , @SubTotalPrice -- <CalcTaxablePrice, float,> , NULL -- <TaxInfo, text,> , 0 -- <UsesExternalCFL, bit,> , 0 -- <IsPricingLocked, bit,> , @BaseProductID -- <BaseProductID, int,> , @ImportDictionary -- <ImportDictionary, varchar(max),> , (CASE WHEN (@ISDerivedProduct = 1) OR (@ImportDictionary IS NOT NULL) THEN 1 ELSE 0 END) -- <ApplyDPChanges, bit,>) ); -- Insert Order Creation into Journal IF (@AddJournal = 1) BEGIN INSERT INTO [Journal] ([ID], [StoreID], [ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EmployeeID],[JournalActivityType] ,[JournalActivityText] ,[Description] ,[Notes] ,[StartDateTime] ,[EndDateTime] ,[TotalTime] ,[ScheduledDateTime] ,[CompletedByID] ,[CompletedDateTime] ,[IsSummary] ,[IsDetail] ,[SummaryID] ,[SummaryClassTypeID] ,[SummaryAmount] ,[DetailAmount] ,[StartGLGroupID] ,[EndGLGroupID] ,[AccountID] ,[AccountClassTypeID] ,[ContactID] ,[ContactClassTypeID] ,[TransactionID] ,[TransactionClassTypeID] ,[IsVoided] ,[VoidedDateTime] ,[VoidedEntryID] ,[VoidedEntryClassTypeID] ,[VoidedReason] ,[QueryStartDateTime] ,[QueryEndDateTime] ,[ReminderDateTime] ,[ReminderPrompt] ,[PartID] ,[ActivityType] ,[ActivityTypeText] ,[IsBillable] ,[BillableDateTime] ,[UseActualTime] ,[BillingNotes] ,[BillingType] ,[TotalBilledTime] ,[RecurringActivityID] ,[LinkID] ,[LinkStoreID] ,[LinkClassTypeID] ,[SpecialCode] ,[DivisionID] ,[HasCalendarLinks] ,[TipRecipientID] ,[PartClassTypeID] ,[RecurringClassTypeID] ,[StationID] ,[StationClassTypeID] ,[CurrentState] ,[StageID] ,[StageClassTypeID]) VALUES ( @JournalID -- (<ID, int,> , -1 -- ,<StoreID, int,> , 20500 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @@ServerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @EmployeeID -- <EmployeeID, int,> , 3 -- <JournalActivityType, int,> , 'Order' -- <JournalActivityText, nvarchar(25),> , 'Line Item Added' -- <Description, nvarchar(50),> , 'Inserted by SQLBridge on '+CONVERT(VARCHAR(20), @DT) -- <Notes, nvarchar(max),> , @DT -- <StartDateTime, datetime,> , @DT -- <EndDateTime, datetime,> , NULL -- <TotalTime, datetime,> , NULL -- <ScheduledDateTime, datetime,> , @EmployeeID -- <CompletedByID, int,> , @DT -- <CompletedDateTime, datetime,> , 1 -- <IsSummary, bit,> , 1 -- <IsDetail, bit,> , NULL -- <SummaryID, int,> , NULL -- <SummaryClassTypeID, int,> , NULL -- <SummaryAmount, decimal(18,4),> , NULL -- <DetailAmount, decimal(18,4),> , NULL -- <StartGLGroupID, int,> , NULL -- <EndGLGroupID, int,> , @AccountID -- <AccountID, int,> , 2000 -- <AccountClassTypeID, int,> , @ContactID -- <ContactID, int,> , 3000 -- <ContactClassTypeID, int,> , @THID -- <TransactionID, int,> , 10000 -- <TransactionClassTypeID, int,> , 0 -- <IsVoided, bit,> , NULL -- <VoidedDateTime, datetime,> , NULL -- <VoidedEntryID, int,> , NULL -- <VoidedEntryClassTypeID, int,> , NULL -- <VoidedReason, nvarchar(max),> , @DT -- <QueryStartDateTime, datetime,> , @DT -- <QueryEndDateTime, datetime,> , NULL -- <ReminderDateTime, datetime,> , 0 -- <ReminderPrompt, bit,> , NULL -- <PartID, int,> , 0 -- <ActivityType, int,> , NULL -- <ActivityTypeText, nvarchar(50),> , 0 -- <IsBillable, bit,> , NULL -- <BillableDateTime, datetime,> , 0 -- <UseActualTime, bit,> , NULL -- <BillingNotes, nvarchar(max),> , 0 -- <BillingType, int,> , 0 -- <TotalBilledTime, float,> , NULL -- <RecurringActivityID, int,> , NULL -- <LinkID, int,> , NULL -- <LinkStoreID, int,> , NULL -- <LinkClassTypeID, int,> , NULL -- <SpecialCode, nvarchar(25),> , @DivisionID -- <DivisionID, int,> , 0 -- <HasCalendarLinks, bit,> , NULL -- <TipRecipientID, int,> , NULL -- <PartClassTypeID, int,> , NULL -- <RecurringClassTypeID, int,> , NULL -- <StationID, int,> , NULL -- <StationClassTypeID, int,> , NULL -- <CurrentState, int,> , NULL -- <StageID, int,> , NULL -- <StageClassTypeID, int,>) ); END; -- IF (@AddJournal=1) -- Now commit the Transaction COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION; INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail) VALUES( NULL, 10100, @THID, 1, 'Line Item '+@ProductName+' Import for Order #'+CONVERT(VARCHAR(12),@OrderNumber)+' FAILED due to Unhandled Exception.', 'Exception: '+ERROR_MESSAGE() ); RETURN; END CATCH; -- -- Step 3. Recalculate the Order if desired; refresh if not. -- IF (@RecomputeOnSave = 1) EXEC dbo.csf_chapi_recompute @THID, 10000, 'Recompute after Import'; IF (@RefreshOnSave = 1) EXEC dbo.csf_chapi_refresh @THID, 10000, -1; -- -- Step 4. Return the New Detail ID -- INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail) VALUES( @TDID, 10100, @THID, 0, 'Line Item '+@ProductName+' imported to Order #'+CONVERT(VARCHAR(12),@OrderNumber), '' ); SELECT * FROM @Logs; RETURN; END;
Notes on Derived Product Import
Derived Product changes are normally applied when the line item is created and are not re-applied after that. When importing into a derived product, this poses a challenge since the line item is created outside of Control and the import routine does not (usually) apply all the configuration from the derived product setup.
To overcome this, when you import into a derived product, you must set the TransDetail.ApplyDPChanges field to 1. When this field is set, Control will re-apply the derived product settings when the order is first edited.
By default in the line item import, this is set for derived products (as of the 6/28 version of the wiki).
Source
Contributor: Cyrious Software
Date: 5/2016
Version: Control 5.75+