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 an estimate in Control.

The following values are required:

@AccountID - The Account.ID that is the database key of the customer the estimate is for. ( e.g. Select ID from Account where CompanyName = 'BigCo' ). This is not the same as the Company Number.
@EstimateDescription - The description of the estimate
@RecomputeOnSave - Set this to 1 to have the estimate to recompute and update on Save. 0 to do it manually afterwards, which is helpful if you are adding additiona line items to the estimate it is created.

The following values may be supplied, but will use the default value is not supplied:

@IsPriceLocked - Set this to 0 to leave the estimate Unlocked so prices will recalculate, set this to 1 to keep the price fixed (Lokced) on edit
@RefreshOnSave - 0 = Don't Refresh on Save, 1 = Do Refresh
@ContactID - The Primary Contact –> AccountContact.ID. If not supplied, the company's primary contact is used.
@BillingAddressID - The billing address. Defaults to the contact's billing address.
@DivisionID - From Division.ID. Use Company Division if not supplied.
@EstimateStationID -
@EstimateCreatedDate - The create date *AND TIME* of the estimate. Leave blank to use current date and time
@EstimateDueDate -
@ProofDueDate -
@Salesperson1ID - First salesperson. House Account by default. From Employee.ID where IsSalerson = 1
@Salesperson2ID - 2nd Salesperson. Not assigned by default.
@Salesperson3ID - 3rd Salesperson. Not assigned by default.
@EnteredByID - Employee who entered estimate. House Account by default. Or select from Employee.ID
@PONumber - PO number for this estimate
@TaxClassID - Leave NULL use the Account's default. Note: Control will recompute Taxes on edit!
@IsTaxExempt - Leave NULL to use the default tax class treatment.
@SaleStationID - Station on sale

You can optionally choose to have the import create a new line item at the same time the estimate is called

by passing in EITHER the ProductName or the ProductID (but not both). The Quantity is then also required but

all other fields are optional.

@ProductName - Product Name. Must match exactly.
@ProductID - Product.ID You must supply either the ProductName of the ProductID.
@ItemQuantity - the quantity of items for ths line
@ItemBasePrice - the extended base price of the line (pre-discount and taxes)
@ItemBasePriceOV - Set to 1 if the price should not be recalculated on edit, 0 if the price can be racalculated on edit.
@ItemDiscount - the discount for this line. Always put the dollar value of the discount here.
@ItemDiscountRate - the discount rate for this line. Only use this if the discount should be adjusted as a percentage. (i.e. 10% = 0.10)
@ItemTax - the amount of taxes for this line item, if applicable.
@ItemDescription - any line item notes for this product
@ItemProductionNotes - any production notes for this product.
@ItemStationID - the line item station ID
@ImageHeight - the height of the product. Leave blank to use the default
@ImageWidth - the width of the proudct

The Stored Procedure returns the ID, ClassTypeID, and EstimateNumber of the new estimate.

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 SQLBridge in estimate 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 creates an Estimate shell (a header with no line items).

code_formatsql

DECLARE @DueDT smalldatetime = '6/4/2016 16:00';

EXEC csp_ImportEstimate

  @AccountID              = 10033,

  @EstimateDescription    = 'Web Estimate #32422',

  @RecomputeOnSave        = 0,

  @EstimateDueDate        = @DueDT,

;

code

This inserts an estimate with one line item and more detail. The estimate is then recalculated.

code_formatsql

DECLARE @ODT smalldatetime = GetDate();

DECLARE @DueDT smalldatetime = '5/28/2016 16:00';

EXEC csp_ImportEstimate

  @AccountID              = 10033,

  @EstimateDescription    = 'Estimate #1234 Imported from XYZ',

  @RecomputeOnSave        = 1,

  @EstimateDueDate        = @DueDT,

  @ProductName            = 'Misc',

  @ItemQuantity           = 5,

  @ItemBasePrice          = 50.00,

  @ItemDiscount           = 4.00,

  @ItemTax                = 3.00,

  @ItemDescription        = 'Radiant!',

  @ItemProductionNotes    = 'This is some great import stuff we''re making here!'

;

code

Stored Procedure

The SQL to create the estimate import stored procedure follows. This must be run to create the stored procedure before it can be used.

code_formatsql

–

– Author: Cyrious Sofware

– Create date: June-2016

– Description: This stored procedure imports an estimate into Control.

– Many of the parameters are option, but if not supplied will used

– The default behavior.

–

– Notes: This routine assumed the following TransHeader UDFs exist

– * Import_Estimate_Number : Numeric → Stores the original Estimate Number

– * Import_Estimate_Date : DateTime → Stores the date the import was run

–

– Returns: New TransHeaderID, TransHeaderClassTypeID, and EstimateNumber

–

CREATE PROCEDURE csp_ImportEstimate

- Required Information

  @AccountID          INT,    -- The Company's ID -> Account.ID of the company

  @EstimateDescription   VARCHAR(256),   -- The description of the estimate

  @RecomputeOnSave    BIT,        -- 1 to have the estimate to recompute and update on Save.  0 to do it manually afterwards.

- Optional Customer/Contact Information

  @ContactID          INT             = NULL,     -- The Primary Contact --> AccountContact.ID.  If not supplied, the company's primary contact is used.

  @BillingAddressID   INT             = NULL,     -- The billing address.  Defaults to the company's billing address.

- Optional estimate Information

  @DivisionID         INT             = 10,       -- From Division.ID.  Use Company Division if not supplied.

  @StatusID           TINYINT         = 11,       -- The status of the imported Estimate. 11 (Pending)

  @DispositionID      TINYINT         = NULL,        -- The disposition of the imported Estimate. NULL (Pending)

  @EstimateStationID  INT             = NULL,

  @EstimateDueDate    SMALLDATETIME   = NULL,

  @ProofDueDate       SMALLDATETIME   = NULL,

  @EstimatePlacedDate smalldatetime   = NULL,     -- The create date *AND TIME* the Estimate was placed.  Leave blank to use current date and time

  @OriginalEstimateNumber    INT         = NULL,     -- The original Estimate number from the source system

  @Salesperson1ID     INT             = 10,       -- First salesperson.  House Account by default.  From Employee.ID where IsSalerson = 1

  @Salesperson2ID     INT             = NULL,     -- 2nd Salesperson.  Not assigned by default.

  @Salesperson3ID     INT             = NULL,     -- 3rd Salesperson.  Not assigned by default.

  @EnteredByID        INT             = 10,       -- Employee who entered estimate.  House Account by default.  Or select from Employee.ID

  @PONumber           VARCHAR(16)     = '',       -- PO number for this estimate

  @SaleStationID      INT             = NULL,     -- Station on sale

  @IsPriceLocked      bit             = 1,        -- 0 = Unlocked, 1 = Locked

  @RefreshOnSave      BIT             = 1,        -- 0 = Don't Refresh on Save, 1 = Do Refresh

  @AllowDuplicates    BIT             = 0,        -- 0 = Don't let an estimate import if there is already an estimate with that OriginalEstimateNumber, 1 = Import Anyway

- Optional Tax Information. If neither TaxClassName or TaxClassID is set, use the Company Tax Class

  @TaxClassName       VARCHAR(255)    = NULL,

  @TaxClassID         INT             = NULL,

  @IsTaxExempt        BIT             = NULL,     -- 1 = Tax Exempt.  Leave NULL to use the default tax class treatment.

- Optional Shipping Information

  @IsShipped          BIT             = 0,        -- Set to 0 if the customer will pick it up, 1 if the estimate will contain a shipment

  @ShippingContactID  INT             = NULL,     -- Contact ID for shipping.  Defaults to Billing Contact.

  @ShippingAddressID  INT             = NULL,     -- Address ID for Shipping.  Defaults to Shipping COntacts' Shipping Address.

  @ShippingPhoneAC    VARCHAR(3)      = NULL,     -- Phone Area code for shipping contact.  Defaults to Shipping Contact's phone.

  @ShippingPhoneNumber VARCHAR(15)    = NULL,     -- Phone number for shipping contact.  Defaults to Shipping Contact's phone.

  @ShippingEmail        VARCHAR(128)    = NULL,        -- Email Address for Shipping Contact.  Defaults to Shipping Contact's email.

  @ShippingNotes      VARCHAR(2048)   = NULL,     -- Shipping notes

  @UseOneTimeShippingAddress  BIT     = 0,        -- Set to 1 if adding a new shiping address.  This address is then added as a one-time use address.

  @SStreetAddress1    VARCHAR(256)    = NULL,

  @SStreetAddress2    VARCHAR(256)    = NULL,

  @SCity              VARCHAR(256)    = NULL,

  @SState             VARCHAR(256)    = NULL,

  @SPostalCode        VARCHAR(256)    = NULL,

- Optional First Line Item

- Set the DECLARE @ProductName or DECLARE @ProductID to automatically add a line item to this estimate.

- For additional options during the import, call the sp_ImportLineItem procedure directly.

  @ProductName            VARCHAR(255)    = NULL,     -- Product Name.  Must match exactly.

  @ProductID              INT             = NULL,     -- Product.ID   You must supply either the ProductName or the ProductID.

  @ItemQuantity           DECIMAL(18,4)    = NULL,    -- the quantity of items for ths line

  @ItemBasePrice          DECIMAL(18,4)    = NULL,    -- the extended base price of the line (pre-discount and taxes)

  @ItemBasePriceOV        BIT             = 1,        -- 1 if the price should not be recalculated on edit, 0 if the price can be racalculated on edit.

  @ItemDiscount           DECIMAL(18,4)    = NULL,    -- the discount for this line.  Always put the dollar value of the discount here.

  @ItemDiscountRate       DECIMAL(18,4)    = NULL,    -- the discount rate for this line.  Only use this if the discount should be adjusted as a percentage. (i.e. 10% = 0.10)

  @ItemTax                DECIMAL(18,4)    = NULL,    -- the amount of taxes for this line item, if applicable.

  @ItemDescription        VARCHAR(4096)   = NULL,     -- any line item notes for this product

  @ItemProductionNotes    VARCHAR(4096)   = NULL,     -- any production notes for this product.

  @ItemStationID          INT             = NULL,     -- the line item station

  @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

- Some OUTPUT Parameters in case the caller wants any of these value back

  @THID                   INT             = NULL   OUTPUT,

  @EstimateNumber         INT             = NULL   OUTPUT,

  @JournalID              INT             = NULL   OUTPUT

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 NOT EXISTS (SELECT 1 FROM Account WHERE ID = @AccountID)

      SET @ValidationError = @ValidationError + 'Company with Account.ID = ' + CONVERT(VARCHAR(12), @AccountID) + ' not found; ';

  IF (@ContactID IS NOT NULL) AND (NOT EXISTS (SELECT 1 FROM AccountContact WHERE ID = @ContactID AND AccountID = @AccountID))

      SET @ValidationError = @ValidationError + 'Contact with ID = ' + CONVERT(VARCHAR(12), @AccountID) + ' does not exist or is for a different company; ';

  IF (@BillingAddressID IS NOT NULL) AND (NOT EXISTS (SELECT 1 FROM Address A JOIN AddressLink AL ON A.ID = AL.AddressID AND A.ID = @BillingAddressID

                                                       WHERE AL.ParentID = @ContactID AND AL.ParentClassTypeID = 3000))

      SET @ValidationError = @ValidationError + 'Address.ID = ' + CONVERT(VARCHAR(12), @BillingAddressID) + ' does not exist or is for a different contact; ';

  IF (@StatusID  11)

      SET @ValidationError = @ValidationError + 'StatusID = ' + CONVERT(VARCHAR(3), @StatusID) + ' is invalid.; ';

  IF (@ProductID IS NOT NULL AND @ProductName IS NOT NULL)

      SET @ValidationError = @ValidationError + 'You may not supply both @ProductName and @ProductID; ';

  IF (@AllowDuplicates = 0 AND @OriginalEstimateNumber IS NOT NULL)

  BEGIN

      DECLARE @PreviousEstimateNumber INT = (SELECT max(TH.EstimateNumber) FROM TransHeaderUserField U join TransHeader TH on TH.ID = U.ID WHERE Import_Estimate_Number = @OriginalEstimateNumber );

      IF (@PreviousEstimateNumber IS NOT NULL)

          SET @ValidationError = @ValidationError + 'Import Failed: Original Estimate '+CONVERT(VARCHAR(12), @OriginalEstimateNumber)

                                      +' already exists as Estimate '+convert(varchar(12),@PreviousEstimateNumber)+ ' ; ';

  END;

  IF (Len(@ValidationError) > 1)

  BEGIN

      INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)

      VALUES(

          NULL, 10000, NULL, 1,

          'Original Estimate '+convert(varchar(12), @OriginalEstimateNumber)+' 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);

  DECLARE @WorkPhoneType          INT             = 10;  -- Business.  From Element Table, where ClassTypeID = 4101

  DECLARE @CellPhoneType          INT             = 12;  -- Mobile.  From Element Table, where ClassTypeID = 4101

  DECLARE @BillingAddressType     INT         = 10;  -- Billing.  From Element Table, where ClassTypeID = xxxx

  DECLARE @ShippingAddressType    INT         = 11;  -- Shipping.  From Element Table, where ClassTypeID = xxxx

- Estimate Related Variables

  SET     @THID                       = (SELECT dbo.csf_chapi_nextid( 10000, 1));

  SET     @EstimateNumber                = (SELECT dbo.csf_chapi_nextnumber( 'EstimateNumber', 1));

  DECLARE @TransactionType            TINYINT = 2; -- 2 for Estimate

  DECLARE @DivisionOV                 BIT             = (CASE WHEN @DivisionID IS NULL THEN 0 ELSE 1 END);

  IF (@DivisionID IS NULL)

      SET @DivisionID = COALESCE( (SELECT DivisionID FROM Account WHERE ID = @AccountID), 10);

- Set Dates

  IF (@EstimatePlacedDate IS NULL)    SET @EstimatePlacedDate  = @DT;

- Set Tax Class on Estimate

  DECLARE @TaxClassOV                 BIT = (CASE WHEN @TaxClassID IS NULL THEN 0 ELSE 1 END);

  DECLARE @IsTaxExemptOV              BIT = (CASE WHEN @IsTaxExempt IS NULL THEN 0 ELSE 1 END);

- Look up the Tax Class ID if not given. If not name is supplied, the function will provide the default.

  IF (@TaxClassID IS NULL)

  BEGIN

      IF (@TaxClassName IS NULL)

          SET @TaxClassID     = COALESCE( (SELECT TaxClassID FROM Account WHERE ID = @AccountID), 50)

      ELSE

          SET @TaxClassID     = dbo.csf_MapTaxClassByName(@TaxClassName);

  END;

- Variation Information

  DECLARE @TVID                       INT             = (SELECT dbo.csf_chapi_nextid( 10400 , 1));

- Company Related Information

  DECLARE @CompanyName                VARCHAR(256)    = (SELECT CompanyName FROM Account WHERE ID = @AccountID);

- Contact Related Information

  DECLARE @ContactOV                  BIT     = (CASE WHEN @ContactID IS NULL THEN 0 ELSE 1 END);

  IF (@ContactID IS NULL)

      SET @ContactID = (SELECT top 1 ID FROM AccountContact WHERE AccountID = @AccountID ORDER BY IsPrimaryContact DESC, IsActive DESC);

  DECLARE @ShippingContactOV          INT     = (CASE WHEN @ShippingContactID IS NULL THEN 0 ELSE 1 END);

  IF (@ShippingContactID IS NULL)     SET @ShippingContactID = @ContactID;

- Address and Phone Related Information

  DECLARE @BillingAddressOV           BIT     = (CASE WHEN @BillingAddressID IS NULL THEN 0 ELSE 1 END);

  DECLARE @ShippingAddressOV          BIT     = (CASE WHEN @ShippingAddressID IS NULL THEN 0 ELSE 1 END);

  DECLARE @BillingAddressLinkID       INT     = (SELECT dbo.csf_chapi_nextid( 4002, 2));

  DECLARE @ShippingAddressLinkID      INT     = @BillingAddressLinkID + 1;

  IF (@UseOneTimeShippingAddress =1)

      SET @ShippingAddressID          = (SELECT dbo.csf_chapi_nextid( 4001, 1))

   ELSE IF (@ShippingAddressID IS NULL)

      SET @ShippingAddressID          = (SELECT ShippingAddressID FROM AccountContact WHERE ID = @ShippingContactID);

  IF (@BillingAddressID IS NULL)

      SET @BillingAddressID           = (SELECT BillingAddressID FROM AccountContact WHERE ID = @ContactID);

  DECLARE @PhoneNumberID              INT        = (SELECT dbo.csf_chapi_nextid( 4100, 3)); -- Work for Company and Contact, Mobile for Contact

- Journal Related Information

  SET     @JournalID                  = (SELECT dbo.csf_chapi_nextid( 20500, 1)); -- Transaction Activity

- Line Item Information

  DECLARE @AddLineItem                BIT     = (CASE WHEN @ProductID IS NULL AND @ProductName IS NULL THEN 0 ELSE 1 END);

- Step 2. Create the Records

  BEGIN TRANSACTION

  BEGIN TRY

- Insert TransHeader

      INSERT INTO [dbo].[TransHeader]

          ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive]

          , [TransactionType] , [EstimateNumber] , [OrderNumber] , [OrderTemplateNumber] , [Version] , [Description] , [AccountID] , [AccountStoreID] , [ContactID] , [SalesPerson1ID] , [SalesPerson2ID] , [SalesPerson3ID] , [OrderNotes] , [PONumber] , [ProductionNotes] , [PricingLevel] , [PricingLevelOverridden] , [DiscountLevel] , [DiscountLevelOverridden] , [EstimateCreatedDate] , [OrderCreatedDate] , [BuiltDate] , [SaleDate] , [ClosedDate] , [VoidedDate] , [ConvertedDate] , [RejectedDate] , [DueDate] , [ProofDate] , [LastFinanceChargeDate] , [DispositionID] , [SalesDispositionID] , [EnteredByID] , [HTMLShortFormat] , [HTMLLongFormat] , [BasePrice] , [DiscountPrice] , [ModifierPrice] , [RawSubTotalPrice] , [RoundingPrice] , [SubTotalPrice] , [TaxesPrice] , [TotalPrice] , [SonsBasePrice] , [SonsDiscountPrice] , [SonsModifierPrice] , [SonsRawSubTotalPrice] , [SonsRoundingPrice] , [SonsSubTotalPrice] , [SonsTaxesPrice] , [SonsTotalPrice] , [FinanceChargeAmount] , [BalanceDue] , [PaymentTotal] , [WriteOffAmount] , [StatusID] , [StatusText] , [TaxClassID] , [TaxClassOverridden] , [InvoiceAddressID] , [InvoiceAddressOverridden] , [InvoiceContactID] , [ShippingAddressID] , [ShippingAddressOverridden] , [ShippingContactID] , [InvoiceAddressLinkID] , [ShippingAddressLinkID] , [EstimateGreeting] , [EstimateGreetingTemplate] , [IsTaxExempt] , [TaxNumber] , [OrderOriginID] , [OrderOriginName] , [ManuallyReOpened] , [CreditMemoAmount] , [CreditMemoOrderID] , [NextRefNumber] , [PricingLevelID] , [PromotionID] , [UseTaxLookup] , [TaxItems] , [InvoiceNumber] , [ServiceTicketNumber] , [DivisionID] , [DivisionIDOverridden] , [OrderNotesTemplate] , [ProductionNotesTemplate] , [IsFirmDueDate] , [ShippingCompanyID] , [ShippingCompanyOV] , [ShippingContactOV] , [InvoiceCompanyID] , [InvoiceCompanyOV] , [InvoiceContactOV] , [UseProgressBilling] , [PercentComplete] , [RecurrenceType] , [RecurrenceEndDate] , [LastRecurrence] , [ScheduledPaymentPlanID] , [ScheduledPaymentDate] , [ScheduledPayments] , [ScheduledPaymentIntervals] , [UsePaymentPlan] , [DroppedDate] , [PurchaseOrderNumber] , [POPaymentTermsID] , [POShippingMethodID] , [RequestedDate] , [ApprovedDate] , [OrderedDate] , [ReceivedDate] , [RequestedByID] , [ApprovedByID] , [OrderedByID] , [ReceivedByID] , [ShipFromAddressLinkID] , [ShipFromAddressID] , [ShipFromID] , [IsValidTax] , [TipsAmount] , [TipsPaidOut] , [IsDirty] , [BillNumber] , [ReceivingDocNumber] , [BillDate] , [ShipFromClassTypeID] , [ShipFromAddressOverridden] , [ExpectedPayID] , [ExpectedPayClassTypeID] , [StationID] , [StationClassTypeID] , [SalesStationID] , [SalesStationClassTypeID] , [DefaultOrderID] , [DefaultOrderClassTypeID] , [DefaultOrderItemID] , [DefaultOrderItemClassTypeID] , [TaxablePrice] , [SonsTaxablePrice] , [StationActivityID] , [StationActivityClassTypeID] , [PayrollID] , [PayrollClassTypeID] , [EarlyPaymentDiscountOVAmount] , [IsEarlyPaymentDiscountOV] , [RecurrenceStartDate] , [IsReturnDocument] , [WarehouseID] , [WarehouseIDOV] , [FinanceChargeTaxXML] , [IsPricingLocked] , [ShipFromPhoneNumberID] , [ShipFromEmailAddress] , [UseShippingAccountInfo] , [ShippingAccountNumber] , [ShippingCarrierID] , [ShippingCarrierClassTypeID] , [ShippingAccountPostalCode] , [ShipmentType] , [ShipmentTypeText] , [IsShippingLocked] , [ShipFromCustomerID] ,[ShipFromCustomerClassTypeID] , [ShipFromDivisionID] , [ShipFromDivisionClassTypeID] , [OverriddenUserFields] , [EstimatingWarehouseID] , [EstimatingWarehouseIDOV] , [ProductionDivisionID] , [ProductionDivisionIDOverridden] , [IsTaxExemptOV] , [ShipToAddressType],[ClonedFromTransactionNumber], [ClonedFromTransactionType], [TaxInfo], [IsPricingLockedAtItems]

      VALUES

      ( @THID  --

      , -1     -- ,

      , 10000     -- ,

      , 'SQLBridge'     -- ,

      , @@ServerName     -- ,

      , @DT    -- ,

      , 0    -- ,

      , 0    -- ,

      , 1    -- ,

      , @TransactionType     --

      , @EstimateNumber     --

      , NULL     --

      , NULL     --

      , NULL     --

      , @EstimateDescription     --

      , @AccountID     --

      , NULL     --

      , @ContactID     --

      , @Salesperson1ID     --

      , @Salesperson2ID     --

      , @Salesperson3ID     --

      , NULL     --

      , @PONumber     --

      , NULL     --

      , 1.0     --

      , 0     --

      , 0.0     --

      , 0     --

      , @EstimatePlacedDate     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , @EstimateDueDate     --

      , @ProofDueDate     --

      , NULL     --

      , NULL     --

      , NULL     --

      , @EnteredByID     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , 0.0     --

      , 0.0     --

      , @StatusID     --

      , NULL     --

      , @TaxClassID     --

      , @TaxClassOV   --

      , @BillingAddressID     --

      , @BillingAddressOV     --

      , @ContactID     --

      , @ShippingAddressID     --

      , @ShippingAddressOV     --

      , @ShippingContactID     --

      , @BillingAddressLinkID     --

      , @ShippingAddressLinkID     --

      , NULL     --

      , NULL     --

      , @IsTaxExempt     --

      , NULL     --

      , NULL     --

      , NULL     --

      , 0     --

      , 0.00     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , 0     --

      , NULL     --

      , NULL     --

      , NULL     --

      , @DivisionID     --

      , @DivisionOV     --

      , NULL     --

      , NULL     --

      , 0     --

      , @AccountID     --

      , 0     --

      , @ShippingContactOV     --

      , @AccountID     --

      , 0     --

      , @ContactOV     --

      , 0     --

      , 0     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , 0    --

      , NULL     --

      , NULL     --

      , 0     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , @EstimateStationID     --

      , 26100     --

      , @SaleStationID     --

      , 26100     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , @IsPriceLocked     --

      , NULL     --

      , @ShippingEmail     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , @DivisionID     --

      , @DivisionOV     --

      , @IsTaxExemptOV     --

      , NULL     --

      , NULL     --

      , NULL     --

      , NULL     --

      , 0        --  )

);

- Insert TransHeaderUDF (Nothing by default)

      INSERT INTO [dbo].[TransHeaderUserField] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] )

      VALUES

      ( @THID  --

      , -1     -- ,

      , 10002     -- ,

      , 'SQLBridge'     -- ,

      , @@ServerName     -- ,

      , @DT    -- ,

      , 0    -- ,

      , 0    -- ,

      , 1    -- ,

);

- Insert TransVariation

      INSERT INTO [dbo].[TransVariation]

          ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive]

          , [TransactionType] , [ParentID] , [ParentClassTypeID] , [VariationName] , [Notes] , [SortIndex] , [CreatedDate] , [PricingLevel] , [PricingLevelOverridden] , [DiscountLevel] , [DiscountLevelOverridden] , [HTMLShortFormat] , [HTMLLongFormat] , [BasePrice] , [DiscountPrice] , [ModifierPrice] , [RawSubTotalPrice] , [RoundingPrice] , [SubTotalPrice] , [TaxesPrice] , [TotalPrice] , [SonsBasePrice] , [SonsDiscountPrice] , [SonsModifierPrice] , [SonsRawSubTotalPrice] , [SonsRoundingPrice] , [SonsSubTotalPrice] , [SonsTaxesPrice] , [SonsTotalPrice] , [TaxablePrice] , [SonsTaxablePrice]

      VALUES

      ( @TVID  --

      , -1     -- ,

      , 10400     -- ,

      , 'SQLBridge'     -- ,

      , @@ServerName     -- ,

      , @DT    -- ,

      , 0    -- ,

      , 0    -- ,

      , 1    -- ,

      , @TransactionType      --

      , @THID      --

      , 10000      --

      , 'Standard'      --

      , NULL      --

      , 0      --

      , @EstimatePlacedDate      --

      , 1.0      --

      , 0      --

      , 0.0      --

      , 0      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

      , NULL      --

);

      IF (@AddLineItem = 1)

      BEGIN

          EXEC csp_ImportLineItem

              @THID               = @THID,

              @ProductName        = @ProductName,

              @ProductID          = @ProductID,

              @Quantity           = @ItemQuantity,

              @BasePrice          = @ItemBasePrice,

              @BasePriceOV        = @ItemBasePriceOV,

              @Discount           = @ItemDiscount,

              @DiscountRate       = @ItemDiscountRate,

              @Tax                = @ItemTax,

              @Description        = @ItemDescription,

              @ProductionNotes    = @ItemProductionNotes,

              @StationID          = @ItemStationID,

              @ImageHeight        = @ImageHeight,

              @ImageWidth         = @ImageWidth,

              @RecomputeOnSave    = 0,

              @RefreshOnSave      = 0,

              @AddJournal         = 0

      END;

- Insert Estimate Creation into Journal

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

          , -1     -- ,

          , 20500     -- ,

          , 'SQLBridge'     -- ,

          , @@ServerName     -- ,

          , @DT    -- ,

          ,  0    -- ,

          ,  0    -- ,

          ,  1    -- ,

         , @EnteredByID  --

         , 4  --

         , 'Estimate'  --

         , 'Estimate Created'  --

         , 'Inserted by SQLBridge on '+CONVERT(VARCHAR(20), @DT)  --

         , @DT  --

         , @DT  --

         , NULL  --

         , NULL  --

         , @EnteredByID  --

         , @DT  --

         , 1  --

         , 1  --

         , NULL  --

         , NULL  --

         , NULL  --

         , NULL  --

         , NULL  --

         , NULL  --

         , @AccountID  --

         , 2000  --

         , @ContactID  --

         , 3000  --

         , @THID --

         , 10000 --

         , 0  --

         , NULL  --

         , NULL  --

         , NULL  --

         , NULL  --

         , @DT  --

         , @DT  --

         , NULL  --

         , 0  --

         , NULL  --

         , 0  --

         , NULL  --

         , 0  --

         , NULL  --

         , 0  --

         , NULL  --

         , 0  --

         , 0  --

         , NULL  --

         , NULL  --

         , NULL  --

         , NULL  --

         , NULL  --

         , @DivisionID  --

         , 0  --

         , NULL  --

         , NULL  --

         , NULL  --

         , NULL  --

         , NULL  --

         , NULL  --

         , NULL  --

         , NULL  -- )

);

- Now Save the Imported Estimate and Date in the UDF Values if those fields exist

      Update TransHeaderUserField

      SET SeqID = SeqID + 1,

          Import_Estimate_Date = @DT,

          Import_Estimate_Number = @OriginalEstimateNumber

      WHERE ID = @THID;

- Now commit the Transaction

  COMMIT TRANSACTION

  END TRY

  BEGIN CATCH

      ROLLBACK TRANSACTION;

      INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)

      VALUES(

          NULL, 10000, NULL, 1,

          'Original Estimate ' + CONVERT(VARCHAR(12),@OriginalEstimateNumber) + '  Import FAILED due to Unhandled Exception.',

          'Exception: ' + ERROR_MESSAGE()

);

  SELECT * FROM @Logs;

  RETURN;

  END CATCH;

- Step 3. Recalculate the Estimate 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 TransHeader ID

  INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)

  VALUES(

      @THID, 10000, NULL, 0,

      'Original Estimate ' + CONVERT(VARCHAR(12),@OriginalEstimateNumber) + ' imported to Estimate #' + CONVERT(VARCHAR(12),@EstimateNumber),

''

);

  SELECT * FROM @Logs;

  RETURN;

END;

code

Source

Contributor: Cyrious Software

Date: 5/2016

Version: Control 5.7+

Table of Contents

Overview

Example Usage

Stored Procedure

Source

See Also