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

Overview

The stored procedure can be used to add a new customer and contact. Not all of the information you can add in Control is included in this standard stored procedure, though someone familiar with SQL could extend this accordingly. All of the records inserted are wrapped in a single transaction. This ensures that the records are either all inserted, or none are inserted.

The stored procedure requires sql_bridge in order to obtain IDs for the inserted records. sql_bridge is a collection of SQL Server stored prodedures and functions and external modules that can be called to safely insert or update data into the Control database and notify the CHAPI service that data has been updated. The CHAPI service is a controller program that will then notify the SSLIP and all copies of Control about the changes.

Example Usage

This inserts a basic customer and contact.

EXEC dbo.csp_ImportCompany
    @CompanyName  = 'Basic Company',
    @URL          = 'www.basics.com',
    -- Office Phone #1
    @WorkPhoneAC      = '225',
    @WorkPhoneNumber  = '7522867',
    -- Company Billing Address Info
    @BStreetAddress1 = '12627 Jefferson Highway',
    @BStreetAddress2  = NULL,
    @BCity            = 'Baton Rouge',
    @BState           = 'LA',
    @BPostalCode      = '70816',
    @Notes         = 'Imported using SQLBridge',
    -- Contact #1 Information
    @AddContact  = 1,     -- Set to 1 to create a contract.  Otherwise you will have to add the contacts manually.
    @Title               = 'Mr.',
    @FirstName           = 'John',
    @LastName           = 'Paul',
    @POSITION          = 'Owner',
    @EmailAddress   = 'jpaul@NewCo.com'

This inserts a sample customer with

  • a different billing and shipping addresses,
  • a cell phone for the contact
  • Some other company info
EXEC dbo.csp_ImportCompany
    @CompanyName  = 'NewCo #5',
    @URL          = 'www.newco.com',
    @PONumber     = '1234',
    @PORequired   = 0, -- false
    @SalespersonID = 10, -- Salesperson1 ID.  10 = House Account, or choose ID from Employee table where IsSalesperson = 1
    -- Office Phone #1
    @WorkPhoneAC      = '225',
    @WorkPhoneNumber  = '7522867',
    -- Company Billing Address Info
    @BStreetAddress1 = '12627 Jefferson Highway',
    @BStreetAddress2  = NULL,
    @BCity            = 'Baton Rouge',
    @BState           = 'LA',
    @BPostalCode      = '70816',
    -- Company Shipping Address Info
    @ShippingAddressSameAsBilling = 0,  -- Set to 1 to use the same shipping as billing address
    @SStreetAddress1  = '9432 Common St',
    @SStreetAddress2  = NULL,
    @SCity            = 'Baton Rouge',
    @SState           = 'LA',
    @SPostalCode      = '70809',
    -- Add other company information
    @DivisionID  = 10,     -- must be an ID from the Division View.  Use 10 if not using Divisions.
    @IndustryID  = NULL,    -- if provided, this must match an industry name in the Element Table
    @IndustryText  = NULL,   -- if provided this must match an *existing* industry.
    @RegionID    = NULL,    -- if provided, this must match an region in the Element Table
    @RegionText    = NULL,   -- if provided this must match an *existing* region.
    @OriginID    = NULL,   -- if provided, this must match an origin in the Element Table
    @OrginText     = NULL,   -- if provided this must match an existing origin.
    @Notes         = 'Imported using SQLBridge',
    @Flags         = 'VIP!',
    -- Contact #1 Information
    @AddContact  = 1,     -- Set to 1 to create a contract.  Otherwise you will have to add the contacts manually.
    @Title               = 'Mr.',
    @FirstName           = 'John',
    @LastName           = 'Paul',
    @POSITION          = 'Owner',
    @EmailAddress   = 'jpaul@NewCo.com',
    -- Contact Additional Phone #1
    @AddCellPhone      = 1,    -- Set to 1 to Add a Cell Phone to the contact
    @CellPhoneAC       = '225',
    @CellPhoneNumber   = '900-8765'

Stored Procedure

-- =============================================
-- Author:        Cyrious Software
-- Create date: May-2016
-- Description:    This stored procedure imports a customer record into Control.
--                Many of the parameters are option, but if not supplied will used
--                The default behavior.
--
-- Returns:     New ID for Customer (Account.ID)
-- =============================================
ALTER PROCEDURE csp_ImportCompany
    -- Required Fields
    @CompanyName  VARCHAR(128),
 
    -- Optional Ccmpany Fields Follow
    @URL          VARCHAR(128) = '',
    @PONumber     VARCHAR(16) = '',
    @PORequired   bit = 0, -- false
    @SalespersonID INT = 10, -- Salesperson1 ID.  10 = House Account, or choose ID from Employee table where IsSalesperson = 1
    @BatchName    VARCHAR(255) = NULL,
    @WorkPhoneAC      VARCHAR(10) = NULL,
    @WorkPhoneNumber  VARCHAR(25) = NULL,
    -- Company Billing Address Info
    @BillingAddress1 VARCHAR(256) = NULL,
    @BillingAddress2 VARCHAR(256) = NULL,
    @BillingCity           VARCHAR(256) = NULL,
    @BillingState          VARCHAR(256) = NULL,
    @BillingPostalCode     VARCHAR(256) = NULL,
    -- Company Shipping Address Info
    @ShippingAddressSameAsBilling bit = 1,  -- Set to 1 to use the same shipping as billing address
    @ShippingAddress1 VARCHAR(256) = NULL,
    @ShippingAddress2 VARCHAR(256) = NULL,
    @ShippingCity           VARCHAR(256) = NULL,
    @ShippingState          VARCHAR(256) = NULL,
    @ShippingPostalCode     VARCHAR(256) = NULL,
    -- Company Tax Class Information.  Set one or none of these, but if you set the both, only the ID is used 
    @TaxClassName VARCHAR(255) = NULL,   
    @TaxClassID   INT          = NULL,    
    -- Add other company information
    @DivisionID   INT = 10,           -- must be an ID from the Division View.  Use 10 if not using Divisions.
 
    @IndustryID   INT = NULL,    -- if provided, this must match an industry name in the Element Table
    @IndustryText VARCHAR(255) = NULL,   -- if provided, this must match an *existing* industry.
    @RegionID     INT = NULL,    -- if provided, this must match an region in the Element Table
    @RegionText   VARCHAR(255) = NULL,   -- if provided, this must match an *existing* region.
    @OriginID     INT = NULL,   -- if provided, this must match an origin in the Element Table
    @OrginText    VARCHAR(255) = NULL,   -- if provided and does not exist, will AUTOMATICALLY add this as an origin
    @Notes        VARCHAR(4096) = '',
    @Flags        VARCHAR(4096) = '',
    -- Contact #1 Information
    @AddContact bit = 1,     -- Set to 1 to create a contract.  Otherwise you will have to add the contacts manually.
    @Title              VARCHAR(25) = NULL,
    @FirstName          VARCHAR(25) = NULL,
    @LastName           VARCHAR(25) = NULL,
    @POSITION           VARCHAR(50) = NULL,
    @EmailAddress       VARCHAR(50) = NULL,
    -- Contact Additional Phone #1
    @AddCellPhone     BIT = 0,    -- Set to 1 to Add a Cell Phone to the contact
    @CellPhoneAC      VARCHAR(10) = NULL,
    @CellPhoneNumber  VARCHAR(25) = NULL,
 
    -- Some OUTPUT Parameters in case the caller wants any of these value back
    @AccountID          INT     = NULL  OUTPUT,
    @ContactID          INT     = NULL  OUTPUT,
    @CompanyNumber      INT		= NULL	OUTPUT,
    @BillingAddressID         INT     = NULL  OUTPUT,
    @ShippingAddressID         INT     = NULL  OUTPUT,
    @JournalID          INT     = NULL  OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Logs                               TABLE( ID INT, ClassTypeID INT, ParentID INT, IsError BIT,
                                                       Summary VARCHAR(255),
                                                       Detail VARCHAR(2000)
                                                       );
    -- Company info Pulled from Store or looked up
    DECLARE @PaymentTermsID       INT;
    DECLARE @PhoneCountryCode     VARCHAR(10);
    -- Retrieve Defaults Information from  databases
    SELECT  TOP 1
            @TaxClassID = COALESCE(DefaultTaxClassID, 50),
            @PhoneCountryCode = COALESCE(DefaultCountryCode, '1')
    FROM Store
    WHERE ID > 0
    ORDER BY ID;
 
    -- Look up the Tax Class ID if not given.  If not name is supplied, the function will provide the default.
    IF (@TaxClassID IS NULL)
        SET @TaxClassID     = dbo.csf_MapTaxClassByName(@TaxClassName);
    -- Declare ID fields used in the process;
    DECLARE @AddressLinkID      INT; -- First Address Link ID
    DECLARE @PhoneNumberID      INT; -- First Phone Number ID
    DECLARE @WorkPhoneType      INT = 10;  -- Business.  From Element Table, where ClassTypeID = 4101
    DECLARE @CellPhoneType      INT = 12;  -- Mobile.  From Element Table, where ClassTypeID = 4101
    -- Define some error variables in case we need them
    DECLARE @DT              SMALLDATETIME = GetDate();
    DECLARE @ComputerName    VARCHAR(25) = @@ServerName;
    DECLARE @NewLine         CHAR(2) =  CHAR(10)+CHAR(13);
-- Step 1, Request New IDs
    SET @AccountID             = (SELECT dbo.csf_chapi_nextid( 2000, 1));
    SET @CompanyNumber         = (SELECT dbo.csf_chapi_nextnumber( 'CompanyNumber', 1));
    SET @BillingAddressID             = (SELECT dbo.csf_chapi_nextid( 4001, 2)); -- Address ID for Billing (and Shipping if needed)
    SET @AddressLinkID         = (SELECT dbo.csf_chapi_nextid( 4002, 4)); -- Address Links for Company (and Contact if needed)
    SET @PhoneNumberID         = (SELECT dbo.csf_chapi_nextid( 4100, 3)); -- Work Phone for Company (and Contact Cell if needed)
    SET @JournalID             = (SELECT dbo.csf_chapi_nextid( 20510, 1)); -- Company Activity
    IF (@AddContact = 1)
        SET @ContactID          = (SELECT dbo.csf_chapi_nextid( 3000, 1));
    IF (@ShippingAddressSameAsBilling = 1)
        SET @ShippingAddressID = @BillingAddressID
    ELSE
        SET @ShippingAddressID = @BillingAddressID + 1;
-- Step 2, Begin a Transaction so the whole thing succeeds or fails together
BEGIN TRANSACTION
   BEGIN TRY
        --
        -- Step 3, Create the associated Company records
        --
        -- Insert Account (Company)
        INSERT INTO [dbo].[Account] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[Notes] ,[CompanyName] ,[AccountNumber] ,[ParentID] ,[Department] ,[DateCreated] ,[DateImported] ,[ImportBatch] ,[AccountingContactID] ,[PrimaryContactID] ,[BillingAddressID] ,[ShippingAddressID] ,[MainPhoneNumberID] ,[MainFaxNumberID] ,[Flags] ,[Keywords] ,[TaxNumber] ,[TaxNumberExpDate] ,[TaxClassID] ,[WebAddress] ,[IsProspect] ,[TaxExempt] ,[HasCreditAccount] ,[CreditApprovalDate] ,[CreditLimit] ,[CreditBalance] ,[PricingPlanTypeID] ,[PaymentTermsID] ,[DiscountLevel] ,[PricingLevel] ,[PONumberRequired] ,[IndustryID] ,[OriginID] ,[Marketing3ID] ,[SalesPersonID1] ,[SalesPersonID2] ,[SalesPersonID3] ,[TaxExemptExpDate] ,[CreditNumber] ,[PricingLevelID] ,[PromotionID] ,[UseTaxLookup] ,[HasServiceContract] ,[ServiceContractStartDate] ,[ServiceContractExpDate] ,[ServiceContractTypeID] ,[ServiceContractNotes] ,[DivisionID] ,[RegionID] ,[PONumber] ,[PrimaryNumber] ,[PriNumberTypeID] ,[PriNumberTypeText] ,[SecondaryNumber] ,[SecNumberTypeID] ,[SecNumberTypeText] ,[IsClient] ,[IsVendor] ,[IsPersonal] ,[Is1099Vendor] ,[VendorPaymentTermsID] ,[MyAccountNumber] ,[DefaultShipMethodID] ,[ThirdNumber] ,[ThirdNumberTypeID] ,[ThirdNumberTypeText] ,[IsFullyTaxExempt] ,[VendorCreditBalance] ,[StageID] ,[StageClassTypeID] ,[StageActivityID] ,[StageActivityClassTypeID] ,[LicenseKey])
        VALUES
        ( @AccountID  -- 
        , -1     -- ,
        , 2000     -- ,
        , 'SQLBridge'     -- ,
        , @ComputerName     -- ,
        , @DT    -- ,
        , 0    -- ,
        , 0    -- ,
        , 1    -- ,
        , @Notes  -- 
        , @CompanyName  -- 
        , @CompanyNumber  -- 
        , NULL  -- 
        , NULL  -- 
        , @DT  -- 
        , @DT  -- 
        , NULL  -- 
        , @ContactID  -- 
        , @ContactID  -- 
        , @BillingAddressID  -- 
        , @ShippingAddressID  -- 
        , @PhoneNumberID  -- 
        , NULL  -- 
        , @Flags  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , @TaxClassID  -- 
        , @URL  -- 
        , 1  -- 
        , 0  -- 
        , 0  -- 
        , NULL  -- 
        , 0  -- 
        , 0  -- 
        , 10  -- 
        , @PaymentTermsID  -- 
        , 0.00  -- 
        , 1.00  -- 
        , @PORequired  -- 
        , @IndustryID  -- 
        , @OriginID  -- 
        , NULL  -- 
        , @SalespersonID  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , 0  -- 
        , 0  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , @DivisionID  -- 
        , @RegionID  -- 
        , @PoNumber  -- 
        , '('+@WorkPhoneAC+') '+@WorkPhoneNumber  -- 
        , @WorkPhoneType  -- 
        , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @WorkPhoneType )  -- 
        , ''  -- 
        , NULL  -- 
        , ''  -- 
        , 0  -- 
        , 0  -- 
        , 0  -- 
        , 0  -- 
        , NULL  -- 
        , ''  -- 
        , NULL  -- 
        , ''  -- 
        , NULL  -- 
        , ''  -- 
        , 0  -- 
        , 0.00  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        );
        -- Insert AccountUDF (Nothing by default)
        INSERT INTO [dbo].[AccountUserField] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] )
        VALUES
        ( @AccountID  -- 
        , -1     -- ,
        , 2001     -- ,
        , 'SQLBridge'     -- ,
        , @ComputerName     -- ,
        , @DT    -- ,
        , 0    -- ,
        , 0    -- ,
        , 1    -- ,
        );
        -- Insert Billing Address and Address Link
        INSERT INTO [dbo].[Address] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [StreetAddress1], [StreetAddress2], [City], [State], [County], [PostalCode], [Country], [FormattedText], [TaxClassID], [IsValidated], [ValidatedAddress], [HasValidationError], [ValidationError])
        VALUES
        ( @BillingAddressID   -- 
        , -1           -- ,
        , 4001         -- ,
        , 'SQLBridge'  -- ,
        , @ComputerName -- ,
        , @DT    -- ,
        , 0            -- ,
        , 0            -- ,
        , 1            -- ,
        , @BillingAddress1   -- 
        , @BillingAddress2   -- 
        , @BillingCity   -- 
        , @BillingState   -- 
        , ''   -- 
        , @BillingPostalCode   -- 
        , 'US'   -- 
        , @BillingAddress1  + @NewLine
           + (CASE WHEN len(COALESCE(@BillingAddress2, '')) > 1 THEN @BillingAddress2 + @NewLine ELSE '' END)
           + @BillingCity + ', ' + @BillingState + '  ' + @BillingPostalCode -- 
        , NULL   -- 
        , 0   -- 
        , NULL   -- 
        , 0   -- 
        , NULL   -- 
        );
        INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany], [CompanyName], [ContactName])
        VALUES
        ( @AddressLinkID + 0   -- 
        , -1           -- ,
        , 4002         -- ,
        , 'SQLBridge'  -- ,
        , @ComputerName -- ,
        , @DT          -- ,
        , 0            -- ,
        , 0            -- ,
        , 1            -- ,
        , 1   -- 
        , @AccountID   -- 
        , 2000   -- 
        , 10   -- Billing Address -- 
        , @BillingAddressID   -- 
        , 'Billing'   -- 
        , 0   -- 
        , NULL   -- 
        , NULL   -- 
        );
        -- Insert Shipping Address if different.  Insert Shipping Addres Link either way
        IF (@ShippingAddressID  @BillingAddressID)
        BEGIN
            INSERT INTO [dbo].[Address] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [StreetAddress1], [StreetAddress2], [City], [State], [County], [PostalCode], [Country], [FormattedText], [TaxClassID], [IsValidated], [ValidatedAddress], [HasValidationError], [ValidationError])
            VALUES
            ( @ShippingAddressID   -- 
            , -1           -- ,
            , 4001         -- ,
            , 'SQLBridge'  -- ,
            , @ComputerName -- ,
            , @DT    -- ,
            , 0            -- ,
            , 0            -- ,
            , 1            -- ,
            , @ShippingAddress1   -- 
            , @ShippingAddress2   -- 
            , @ShippingCity   -- 
            , @ShippingState   -- 
            , ''   -- 
            , @ShippingPostalCode   -- 
            , 'US'   -- 
            , @ShippingAddress1  + @NewLine
            + (CASE WHEN len(COALESCE(@ShippingAddress2, '')) > 1 THEN @ShippingAddress2 + @NewLine ELSE '' END)
            + @ShippingCity + ', ' + @ShippingState + '  ' + @ShippingPostalCode -- 
            , NULL   -- 
            , 0   -- 
            , NULL   -- 
            , 0   -- 
            , NULL   -- 
            );
        END;
        INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany], [CompanyName], [ContactName])
        VALUES
        ( @AddressLinkID + 1   -- 
        , -1           -- ,
        , 4002         -- ,
        , 'SQLBridge'  -- ,
        , @ComputerName -- ,
        , @DT          -- ,
        , 0            -- ,
        , 0            -- ,
        , 1            -- ,
        , CASE WHEN (@ShippingAddressID = @BillingAddressID) THEN 0 ELSE 1 END  -- 
        , @AccountID   -- 
        , 2000   -- 
        , 11   -- Shipping Address -- 
        , @ShippingAddressID   -- 
        , 'Shipping'   -- 
        , 0   -- 
        , NULL   -- 
        , NULL   -- 
        );
        -- Insert Business Phone Number
        INSERT INTO [dbo].[PhoneNumber]  ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [ParentID], [ParentStoreID], [ParentClassTypeID], [PhoneNumberTypeID], [CountryCode], [AreaCode], [PhoneNumber], [Extension], [FormattedText], [PhoneNumberIndex], [PhoneNumberTypeText])
        VALUES
        ( @PhoneNumberID+0 -- 
        , -1           -- ,
        , 4100         -- ,
        , 'SQLBridge'  -- ,
        , @ComputerName -- ,
        , @DT          -- ,
        , 0            -- ,
        , 0            -- ,
        , 1            -- ,
        , @AccountID   -- 
        , -1     -- 
        , 2000   -- 
        , @WorkPhoneType   -- 
        , '1'  -- 
        , @WorkPhoneAC   -- 
        , @WorkPhoneNumber   -- 
        , ''   -- 
        , '('+@WorkPhoneAC+') '+@WorkPhoneNumber   -- 
        , 0   -- 
        , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @WorkPhoneType) -- 
        );
        --
        -- Step 4, Create the associated CONTACT RECORDS
        --
        IF (@AddContact = 1)
        BEGIN
            -- Insert Contact
            INSERT INTO [dbo].[AccountContact] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[Notes] ,[FirstName] ,[LastName] ,[Title] ,[POSITION], [EmailAddress] ,[MainPhoneNumberID] ,[MainFaxNumberID] ,[AccountID] ,[IsPrimaryContact] ,[IsAccountingContact] ,[BillingAddressID] ,[ShippingAddressID] ,[BirthDateMonth] ,[BirthDateDay] ,[DefaultPaymentExpDate] ,[DefaultPaymentTrackingNumber] ,[DefaultPaymentNameOnCard] ,[DefaultPaymentTypeID] ,[CCBillingAddress] ,[DefaultPaymentVCode] ,[UserID] ,[SalespersonID1] ,[SalespersonID2] ,[SalespersonID3] ,[UseCompanySalespeople] ,[IsCCNumEncrypt] ,[DisplayNumber] ,[BirthDate] ,[IsVCodeEncrypted] ,[PrimaryNumber] ,[PriNumberTypeID] ,[PriNumberTypeText] ,[SecondaryNumber] ,[SecNumberTypeID] ,[SecNumberTypeText] ,[PaymentAddressID] ,[CCSwiped] ,[SendBillingAddress] ,[IDNumber] ,[ImageID] ,[MiddleName] ,[ContactType] ,[GenderType] ,[NumOfMakeups] ,[ThirdNumber] ,[ThirdNumberTypeID] ,[ThirdNumberTypeText] ,[DefaultPaymentBankReference] ,[DefaultPaymentBankCode] ,[DefaultPaymentBranchCode] ,[DefaultPaymentCIN] ,[DefaultPaymentState] ,[DefaultPaymentCCAccount] ,[PaymentAddressLinkID] ,[CCCSCustomerGuid] ,[UseShippingAccountInfo] ,[DefaultShippingAccountNumber] ,[DefaultShippingCarrierID] ,[DefaultShippingCarrierClassTypeID] ,[DefaultShippingAccountPostalCode] ,[ShippingMethodLinksXML] ,[PaymentAddressOV])
            VALUES
            ( @ContactID  -- 
            , -1     -- ,
            , 3000     -- ,
            , 'SQLBridge'     -- ,
            , @ComputerName     -- ,
            , @DT          -- ,
            , 0    -- ,
            , 0    -- ,
            , 1    -- ,
            , NULL  -- 
            , @FirstName  -- 
            , @LastName  -- 
            , @Title  -- 
            , @POSITION  -- 
            , @EmailAddress  -- 
            , @PhoneNumberID+1  -- 
            , CASE WHEN (@AddCellPhone = 1) THEN @PhoneNumberID+2 ELSE NULL END  -- 
            , @AccountID  -- 
            , 1  -- 
            , 1  -- 
            , @BillingAddressID  -- 
            , @ShippingAddressID  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , @SalespersonID  -- 
            , NULL  -- 
            , NULL  -- 
            , 1  -- 
            , 1  -- 
            , ''  -- 
            , NULL  -- 
            , 1  -- 
            , '('+@WorkPhoneAC+') '+@WorkPhoneNumber  -- 
            , @WorkPhoneType  -- 
            , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @WorkPhoneType) -- 
            , CASE WHEN (@AddCellPhone = 1) THEN '('+@CellPhoneAC+') '+@CellPhoneNumber ELSE '' END  -- 
            , @CellPhoneType  -- 
            , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @CellPhoneType) -- 
            , NULL  -- 
            , 0  -- 
            , 1  -- 
            , NULL  -- 
            , NULL  -- 
            , ''  -- 
            , 0  -- 
            , 0  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , NULL  -- 
            , 0  -- 
            , NULL  -- 
            , -1  -- 
            , -1  -- 
            , NULL  -- 
            , NULL  -- 
            , 0  -- 
            );
            -- Insert ContactUDF (Nothing by default)
            INSERT INTO [dbo].[AccountContactUserField] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] )
            VALUES
            ( @ContactID  -- 
            , -1     -- ,
            , 3001     -- ,
            , 'SQLBridge'     -- ,
            , @ComputerName     -- ,
            , @DT  -- ,
            , 0    -- ,
            , 0    -- ,
            , 1    -- ,
            );
            -- Insert Contact Address Links  (Billing)
            INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany], [CompanyName], [ContactName])
            VALUES
            ( @AddressLinkID + 2   -- 
            , -1           -- ,
            , 4002         -- ,
            , 'SQLBridge'  -- ,
            , @ComputerName -- ,
            , @DT          -- ,
            , 0            -- ,
            , 0            -- ,
            , 1            -- ,
            , 0   -- 
            , @ContactID   -- 
            , 3000   -- 
            , 10   -- Shipping Address -- 
            , @BillingAddressID   -- 
            , 'Billing'   -- 
            , 0   -- 
            , NULL   -- 
            , NULL   -- 
            );
            -- Insert Contact Address Links  (Shipping)
            INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany], [CompanyName], [ContactName])
            VALUES
            ( @AddressLinkID + 3   -- 
            , -1           -- ,
            , 4002         -- ,
            , 'SQLBridge'  -- ,
            , @ComputerName -- ,
            , @DT          -- ,
            , 0            -- ,
            , 0            -- ,
            , 1            -- ,
            , 0   -- 
            , @ContactID   -- 
            , 3000   -- 
            , 11   -- Shipping Address -- 
            , @ShippingAddressID   -- 
            , 'Shipping'   -- 
            , 0   -- 
            , NULL   -- 
            , NULL   -- 
            );
            -- Insert Contact Phone Numbers (Work and Cell)
            INSERT INTO [dbo].[PhoneNumber]  ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [ParentID], [ParentStoreID], [ParentClassTypeID], [PhoneNumberTypeID], [CountryCode], [AreaCode], [PhoneNumber], [Extension], [FormattedText], [PhoneNumberIndex], [PhoneNumberTypeText])
            VALUES
            ( @PhoneNumberID+1 -- 
            , -1           -- ,
            , 4100         -- ,
            , 'SQLBridge'  -- ,
            , @ComputerName -- ,
            , @DT          -- ,
            , 0            -- ,
            , 0            -- ,
            , 1            -- ,
            , @ContactID   -- 
            , -1     -- 
            , 3000   -- 
            , @WorkPhoneType   -- 
            , '1'  -- 
            , @WorkPhoneAC   -- 
            , @WorkPhoneNumber   -- 
            , ''   -- 
            , '('+@WorkPhoneAC+') '+@WorkPhoneNumber   -- 
            , 0   -- 
            , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @WorkPhoneType) -- 
            );
            IF (@AddCellPhone = 1)
            BEGIN
                INSERT INTO [dbo].[PhoneNumber]  ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [ParentID], [ParentStoreID], [ParentClassTypeID], [PhoneNumberTypeID], [CountryCode], [AreaCode], [PhoneNumber], [Extension], [FormattedText], [PhoneNumberIndex], [PhoneNumberTypeText])
                VALUES
                ( @PhoneNumberID+2 -- 
                , -1           -- ,
                , 4100         -- ,
                , 'SQLBridge'  -- ,
                , @ComputerName -- ,
                , GetDate()    -- ,
                , 0            -- ,
                , 0            -- ,
                , 1            -- ,
                , @ContactID   -- 
                , -1     -- 
                , 3000   -- 
                , @CellPhoneType   -- 
                , '1'  -- 
                , @CellPhoneAC   -- 
                , @CellPhoneNumber   -- 
                , ''   -- 
                , '('+@CellPhoneAC+') '+@CellPhoneNumber   -- 
                , 0   -- 
                , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @CellPhoneType) -- 
                );
            END; -- of adding cell phone
        END; -- Of adding contact records
        --
        -- Step 4, Create a not in the JOURNAL record
        --
        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     -- ,
            , 20510     -- ,
            , 'SQLBridge'     -- ,
            , @ComputerName     -- ,
            , @DT    -- ,
            ,  0    -- ,
            ,  0    -- ,
            ,  1    -- ,
           , @SalespersonID  -- 
           , 6  -- 
           , 'Company'  -- 
           , 'Company Created'  -- 
           , 'Inserted by SQLBridge'  -- 
           , @DT  -- 
           , @DT  -- 
           , NULL  -- 
           , NULL  -- 
           , @SalespersonID  -- 
           , @DT  -- 
           , 1  -- 
           , 1  -- 
           , NULL  -- 
           , NULL  -- 
           , NULL  -- 
           , NULL  -- 
           , NULL  -- 
           , NULL  -- 
           , @AccountID  -- 
           , 2000  -- 
           , @ContactID  -- 
           , 3000  -- 
           , NULL  -- 
           , NULL  -- 
           , 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  -- )
        );
        --
        -- Step 5, COMMIT the transaction to the database
        --
        COMMIT TRANSACTION
    END TRY
    --
    -- Step 6, Handle ERRORS by cancelling the transaction and re-raising the error
    --
    BEGIN CATCH
        ROLLBACK TRANSACTION;
 
        INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
        VALUES(
            NULL, 2000, NULL, 1,
            'Company Name ['+@CompanyName+'] Import FAILED due to Unhandled Exception.',
            'Exception: '+ERROR_MESSAGE()
        );
        RETURN;
    END CATCH;
    --
    -- Step 7, REFRESH the Order Record to let copies of Control konw there is a new company
    --
    EXEC dbo.csf_chapi_refresh @AccountID, 2000, 0;
     -- Now Return Displayable Results
    INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
    VALUES(
        @AccountID, 2000, NULL, 0,
        'Company Name ['+@CompanyName+'] Imported as Company Number '+CONVERT(VARCHAR(12), @CompanyNumber),
        ''
    );
    SELECT * FROM @Logs;
    RETURN;
 
END
GO

Contributor: Cyrious Software

Date: 5/2016

Version: Control 5.7+

You could leave a comment if you were logged in.