Table of Contents

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

code_formatsql

exec dbo.csp_ImportCompany 

  @CompanyName  = 'Basic Company',
  @URL          = 'www.basics.com',
  1. - Office Phone #1
  @WorkPhoneAC      = '225',
  @WorkPhoneNumber  = '7522867',
  1. - Company Billing Address Info
  @BStreetAddress1 = '12627 Jefferson Highway',
  @BStreetAddress2  = NULL,
  @BCity            = 'Baton Rouge',
  @BState           = 'LA',
  @BPostalCode      = '70816',
  @Notes         = 'Imported using SQLBridge',
  1. - 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'

code

This inserts a sample customer with

* a different billing and shipping addresses,

code_formatsql

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
  1. - Office Phone #1
  @WorkPhoneAC      = '225',
  @WorkPhoneNumber  = '7522867',
  1. - Company Billing Address Info
  @BStreetAddress1 = '12627 Jefferson Highway',
  @BStreetAddress2  = NULL,
  @BCity            = 'Baton Rouge',
  @BState           = 'LA',
  @BPostalCode      = '70816',
  1. - 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',
  1. - 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!',
  1. - 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',
  1. - Contact Additional Phone #1
  @AddCellPhone      = 1,    -- Set to 1 to Add a Cell Phone to the contact
  @CellPhoneAC       = '225',
  @CellPhoneNumber   = '900-8765'

code

Stored Procedure

code_formatsql

–

– 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

  1. - Required Fields
  @CompanyName  VARCHAR(128),
  1. - 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,
  1. - Company Billing Address Info
  @BillingAddress1 VARCHAR(256) = NULL,
  @BillingAddress2 VARCHAR(256) = NULL,
  @BillingCity           VARCHAR(256) = NULL,
  @BillingState          VARCHAR(256) = NULL,
  @BillingPostalCode     VARCHAR(256) = NULL,
  1. - 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,
  1. - 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,
  1. - 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) = '',
  1. - 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,
  1. - 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,
  1. - 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)
                                                     );
  1. - Company info Pulled from Store or looked up
  DECLARE @PaymentTermsID       INT;
  DECLARE @PhoneCountryCode     VARCHAR(10);
  1. - Retrieve Defaults Information from databases
  SELECT  TOP 1
          @TaxClassID = COALESCE(DefaultTaxClassID, 50),
          @PhoneCountryCode = COALESCE(DefaultCountryCode, '1')
  FROM Store
  WHERE ID > 0
  ORDER BY ID;
  1. - 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);
  1. - 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
  1. - 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
  1. -
  1. - Step 3, Create the associated Company records
  1. -
  1. - 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  -- 
      );
  1. - 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    -- ,
      );
  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   -- 
      );
  1. - 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   -- 
      );
  1. - 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) -- 
      );
  1. -
  1. - Step 4, Create the associated CONTACT RECORDS
  1. -
      IF (@AddContact = 1)
      BEGIN
  1. - 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  -- 
          );
  1. - 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    -- ,
          );
  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   -- 
          );
  1. - 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   -- 
          );
  1. - 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
  1. -
  1. - Step 4, Create a not in the JOURNAL record
  1. -
      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  -- )
      );
  1. -
  1. - Step 5, COMMIT the transaction to the database
  1. -
      COMMIT TRANSACTION
  END TRY
  1. -
  1. - Step 6, Handle ERRORS by cancelling the transaction and re-raising the error
  1. -
  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;
  1. -
  1. - Step 7, REFRESH the Order Record to let copies of Control konw there is a new company
  1. -
  EXEC dbo.csf_chapi_refresh @AccountID, 2000, 0;
  1. - 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

code

Source

Contributor: Cyrious Software

Date: 5/2016

Version: Control 5.7+

See Also