SQL Stored Procedure - Import Estimate
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 afterward, which is helpful if you are adding additional 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 (Locked) 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 an 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 the 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 recalculated 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 product
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 sql_bridge in estimate to obtain IDs for the inserted records. sql_bridge is a collection of SQL Server stored procedures and functions and external modules that can be called to safely insert or update data into the Control database and notify the CHAPI service that data has been updated. The CHAPI service is a controller program that will then notify the SSLIP and all copies of Control about the changes.
Example Usage
This creates an Estimate shell (a header with no line items).
DECLARE @DueDT smalldatetime = '6/4/2016 16:00'; EXEC csp_ImportEstimate @AccountID = 10033, @EstimateDescription = 'Web Estimate #32422', @RecomputeOnSave = 0, @EstimateDueDate = @DueDT, ;
This inserts an estimate with one line item and more detail. The estimate is then recalculated.
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!' ;
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.
-- ============================================= -- 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 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 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 -- <ID, int,> , -1 -- ,<StoreID, int,> , 10000 -- ,<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,> , @EstimateNumber -- <EstimateNumber, int,> , NULL -- <OrderNumber, int,> , NULL -- <OrderTemplateNumber, int,> , NULL -- <Version, int,> , @EstimateDescription -- <Description, varchar(max),> , @AccountID -- <AccountID, int,> , NULL -- <AccountStoreID, int,> , @ContactID -- <ContactID, int,> , @Salesperson1ID -- <SalesPerson1ID, int,> , @Salesperson2ID -- <SalesPerson2ID, int,> , @Salesperson3ID -- <SalesPerson3ID, int,> , NULL -- <OrderNotes, varchar(max),> , @PONumber -- <PONumber, varchar(25),> , NULL -- <ProductionNotes, varchar(max),> , 1.0 -- <PricingLevel, decimal(18,4),> , 0 -- <PricingLevelOverridden, bit,> , 0.0 -- <DiscountLevel, decimal(18,4),> , 0 -- <DiscountLevelOverridden, bit,> , @EstimatePlacedDate -- <EstimateCreatedDate, smalldatetime,> , NULL -- <OrderCreatedDate, smalldatetime,> , NULL -- <BuiltDate, smalldatetime,> , NULL -- <SaleDate, smalldatetime,> , NULL -- <ClosedDate, smalldatetime,> , NULL -- <VoidedDate, smalldatetime,> , NULL -- <ConvertedDate, smalldatetime,> , NULL -- <RejectedDate, smalldatetime,> , @EstimateDueDate -- <DueDate, smalldatetime,> , @ProofDueDate -- <ProofDate, smalldatetime,> , NULL -- <LastFinanceChargeDate, smalldatetime,> , NULL -- <DispositionID, int,> , NULL -- <SalesDispositionID, int,> , @EnteredByID -- <EnteredByID, int,> , NULL -- <HTMLShortFormat, varchar(max),> , NULL -- <HTMLLongFormat, varchar(max),> , NULL -- <BasePrice, decimal(18,4),> , NULL -- <DiscountPrice, decimal(18,4),> , NULL -- <ModifierPrice, decimal(18,4),> , NULL -- <RawSubTotalPrice, decimal(18,4),> , NULL -- <RoundingPrice, decimal(18,4),> , NULL -- <SubTotalPrice, decimal(18,4),> , NULL -- <TaxesPrice, decimal(18,4),> , NULL -- <TotalPrice, decimal(18,4),> , NULL -- <SonsBasePrice, decimal(18,4),> , NULL -- <SonsDiscountPrice, decimal(18,4),> , NULL -- <SonsModifierPrice, decimal(18,4),> , NULL -- <SonsRawSubTotalPrice, decimal(18,4),> , NULL -- <SonsRoundingPrice, decimal(18,4),> , NULL -- <SonsSubTotalPrice, decimal(18,4),> , NULL -- <SonsTaxesPrice, decimal(18,4),> , NULL -- <SonsTotalPrice, decimal(18,4),> , NULL -- <FinanceChargeAmount, decimal(18,4),> , NULL -- <BalanceDue, decimal(18,4),> , 0.0 -- <PaymentTotal, decimal(18,4),> , 0.0 -- <WriteOffAmount, decimal(18,4),> , @StatusID -- <StatusID, int,> , NULL -- <StatusText, varchar(50),> , @TaxClassID -- <TaxClassID, int,> , @TaxClassOV -- <TaxClassOverridden, bit,> , @BillingAddressID -- <InvoiceAddressID, int,> , @BillingAddressOV -- <InvoiceAddressOverridden, bit,> , @ContactID -- <InvoiceContactID, int,> , @ShippingAddressID -- <ShippingAddressID, int,> , @ShippingAddressOV -- <ShippingAddressOverridden, bit,> , @ShippingContactID -- <ShippingContactID, int,> , @BillingAddressLinkID -- <InvoiceAddressLinkID, int,> , @ShippingAddressLinkID -- <ShippingAddressLinkID, int,> , NULL -- <EstimateGreeting, varchar(max),> , NULL -- <EstimateGreetingTemplate, varchar(max),> , @IsTaxExempt -- <IsTaxExempt, bit,> , NULL -- <TaxNumber, varchar(50),> , NULL -- <OrderOriginID, int,> , NULL -- <OrderOriginName, varchar(50),> , 0 -- <ManuallyReOpened, bit,> , 0.00 -- <CreditMemoAmount, decimal(18,4),> , NULL -- <CreditMemoOrderID, int,> , NULL -- <NextRefNumber, int,> , NULL -- <PricingLevelID, int,> , NULL -- <PromotionID, int,> , 0 -- <UseTaxLookup, bit,> , NULL -- <TaxItems, varchar(max),> , NULL -- <InvoiceNumber, int,> , NULL -- <ServiceTicketNumber, int,> , @DivisionID -- <DivisionID, int,> , @DivisionOV -- <DivisionIDOverridden, bit,> , NULL -- <OrderNotesTemplate, varchar(max),> , NULL -- <ProductionNotesTemplate, varchar(max),> , 0 -- <IsFirmDueDate, bit,> , @AccountID -- <ShippingCompanyID, int,> , 0 -- <ShippingCompanyOV, bit,> , @ShippingContactOV -- <ShippingContactOV, bit,> , @AccountID -- <InvoiceCompanyID, int,> , 0 -- <InvoiceCompanyOV, bit,> , @ContactOV -- <InvoiceContactOV, bit,> , 0 -- <UseProgressBilling, bit,> , 0 -- <PercentComplete, decimal(18,4),> , NULL -- <RecurrenceType, int,> , NULL -- <RecurrenceEndDate, smalldatetime,> , NULL -- <LastRecurrence, smalldatetime,> , NULL -- <ScheduledPaymentPlanID, int,> , NULL -- <ScheduledPaymentDate, smalldatetime,> , NULL -- <ScheduledPayments, int,> , NULL -- <ScheduledPaymentIntervals, int,> , NULL -- <UsePaymentPlan, bit,> , NULL -- <DroppedDate, smalldatetime,> , NULL -- <PurchaseOrderNumber, int,> , NULL -- <POPaymentTermsID, int,> , NULL -- <POShippingMethodID, int,> , NULL -- <RequestedDate, smalldatetime,> , NULL -- <ApprovedDate, smalldatetime,> , NULL -- <OrderedDate, smalldatetime,> , NULL -- <ReceivedDate, smalldatetime,> , NULL -- <RequestedByID, int,> , NULL -- <ApprovedByID, int,> , NULL -- <OrderedByID, int,> , NULL -- <ReceivedByID, int,> , NULL -- <ShipFromAddressLinkID, int,> , NULL -- <ShipFromAddressID, int,> , NULL -- <ShipFromID, int,> , 0 -- <IsValidTax, bit,> , NULL -- <TipsAmount, decimal(18,4),> , NULL -- <TipsPaidOut, decimal(18,4),> , 0 -- <IsDirty, bit,> , NULL -- <BillNumber, int,> , NULL -- <ReceivingDocNumber, int,> , NULL -- <BillDate, smalldatetime,> , NULL -- <ShipFromClassTypeID, int,> , NULL -- <ShipFromAddressOverridden, bit,> , NULL -- <ExpectedPayID, int,> , NULL -- <ExpectedPayClassTypeID, int,> , @EstimateStationID -- <StationID, int,> , 26100 -- <StationClassTypeID, int,> , @SaleStationID -- <SalesStationID, int,> , 26100 -- <SalesStationClassTypeID, int,> , NULL -- <DefaultOrderID, int,> , NULL -- <DefaultOrderClassTypeID, int,> , NULL -- <DefaultOrderItemID, int,> , NULL -- <DefaultOrderItemClassTypeID, int,> , NULL -- <TaxablePrice, decimal(18,4),> , NULL -- <SonsTaxablePrice, decimal(18,4),> , NULL -- <StationActivityID, int,> , NULL -- <StationActivityClassTypeID, int,> , NULL -- <PayrollID, int,> , NULL -- <PayrollClassTypeID, int,> , NULL -- <EarlyPaymentDiscountOVAmount, decimal(18,4),> , NULL -- <IsEarlyPaymentDiscountOV, bit,> , NULL -- <RecurrenceStartDate, smalldatetime,> , NULL -- <IsReturnDocument, bit,> , NULL -- <WarehouseID, int,> , NULL -- <WarehouseIDOV, bit,> , NULL -- <FinanceChargeTaxXML, xml,> , @IsPriceLocked -- <IsPricingLocked, bit,> , NULL -- <ShipFromPhoneNumberID, int,> , @ShippingEmail -- <ShipFromEmailAddress, varchar(50),> , NULL -- <UseShippingAccountInfo, bit,> , NULL -- <ShippingAccountNumber, varchar(100),> , NULL -- <ShippingCarrierID, int,> , NULL -- <ShippingCarrierClassTypeID, int,> , NULL -- <ShippingAccountPostalCode, varchar(25),> , NULL -- <ShipmentType, int,> , NULL -- <ShipmentTypeText, varchar(25),> , NULL -- <IsShippingLocked, bit,> , NULL -- <ShipFromCustomerID, int,> , NULL -- <ShipFromCustomerClassTypeID, int,> , NULL -- <ShipFromDivisionID, int,> , NULL -- <ShipFromDivisionClassTypeID, int,> , NULL -- <OverriddenUserFields, varchar(max),> , NULL -- <EstimatingWarehouseID, int,> , NULL -- <EstimatingWarehouseIDOV, bit,> , @DivisionID -- <ProductionDivisionID, int,> , @DivisionOV -- <ProductionDivisionIDOverridden, bit,> , @IsTaxExemptOV -- <IsTaxExemptOV, bit,> , NULL -- <ShipToAddressType, int,> , NULL -- <ClonedFromTransactionNumber, int,> , NULL -- <ClonedFromTransactionType, int,> , NULL -- <TaxInfo, text,> , 0 -- <IsPricingLockedAtItems, bit,>) ); -- Insert TransHeaderUDF (Nothing by default) INSERT INTO [dbo].[TransHeaderUserField] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ) VALUES ( @THID -- <ID, int,> , -1 -- ,<StoreID, int,> , 10002 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @@ServerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> ); -- 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 -- <ID, int,> , -1 -- ,<StoreID, int,> , 10400 -- ,<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,> , 'Standard' -- <VariationName, varchar(50),> , NULL -- <Notes, varchar(max),> , 0 -- <SortIndex, int,> , @EstimatePlacedDate -- <CreatedDate, smalldatetime,> , 1.0 -- <PricingLevel, decimal(18,4),> , 0 -- <PricingLevelOverridden, bit,> , 0.0 -- <DiscountLevel, decimal(18,4),> , 0 -- <DiscountLevelOverridden, bit,> , NULL -- <HTMLShortFormat, varchar(max),> , NULL -- <HTMLLongFormat, varchar(max),> , NULL -- <BasePrice, decimal(18,4),> , NULL -- <DiscountPrice, decimal(18,4),> , NULL -- <ModifierPrice, decimal(18,4),> , NULL -- <RawSubTotalPrice, decimal(18,4),> , NULL -- <RoundingPrice, decimal(18,4),> , NULL -- <SubTotalPrice, decimal(18,4),> , NULL -- <TaxesPrice, decimal(18,4),> , NULL -- <TotalPrice, decimal(18,4),> , NULL -- <SonsBasePrice, decimal(18,4),> , NULL -- <SonsDiscountPrice, decimal(18,4),> , NULL -- <SonsModifierPrice, decimal(18,4),> , NULL -- <SonsRawSubTotalPrice, decimal(18,4),> , NULL -- <SonsRoundingPrice, decimal(18,4),> , NULL -- <SonsSubTotalPrice, decimal(18,4),> , NULL -- <SonsTaxesPrice, decimal(18,4),> , NULL -- <SonsTotalPrice, decimal(18,4),> , NULL -- <TaxablePrice, decimal(18,4),> , NULL -- <SonsTaxablePrice, decimal(18,4),> ); 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 -- (<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,> , @EnteredByID -- <EmployeeID, int,> , 4 -- <JournalActivityType, int,> , 'Estimate' -- <JournalActivityText, nvarchar(25),> , 'Estimate Created' -- <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,> , @EnteredByID -- <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,>) ); -- 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; GO
Source
Contributor: Cyrious Software
Date: 5/2016
Version: Control 5.7+
You could leave a comment if you were logged in.