WARNING: This is not kids stuff. Use of this feature requires expertise in SQL Server and in the Cyrious database structure. It is very possible to irreversibly damage your Cyrious Control database if you make a mistake. Always develop your tests using a separate database on a separate machine. If you are interested in using sql_bridge but are not a SQL guru, please contact a sales or consulting at Cyrious.

Note: This feature requires Control 5.75 or higher.

The stored procedure can be used to 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.

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;

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).

Contributor: Cyrious Software

Date: 5/2016

Version: Control 5.75+