Table of Contents

{$page}

WARNING: If you are interested in using SQL Bridge, please contact a sales or implementation consultant at Cyrious.

Overview

Cyrious Control's SQLBridge 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.

Caution: 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.
Caution: Always wrap multi-record operations with SQL transaction statements. This ensure the integrity of the entire update by preventing partial changes or records from being saved. If you are not familiar with using SQL transactions, STOP! Do not proceed with any database insertions, deletions, or updates until you are.
Example

This example adds a new company called “SQL Bridger” with a contact “Handy Bridge”. A single address for the company and contact is used, the work phone number is for both the company and contact, but the cell phone of the contact is also added.

code_formatsql

– select * from AddressLink where ParentID in (1001, 1005) and ParentClassTypeID = 2000

–

– This example adds a new company called “SQL Bridger” with a contact “Handy Bridge”.

– A single address for the company and contact is used, the work phone number is for

– both the company and contact, but the cell phone of the contact is also added.

– Step 1, Fill in initial data. This could be inserted direclty in the data below

– but using variables makes for cleaner code

  1. - Entered Company Information
  DECLARE @DivisionID   int = 10;           -- must be an ID from the Division View
  DECLARE @CompanyName  varchar(128) = 'SQL Bridge';
  DECLARE @IsActive     bit = 1; -- true
  DECLARE @IsProspect   bit = 1; -- true  (note, IsClient set automatically on first order)
  DECLARE @IsClient     bit = 0; -- false  (note, IsClient set automatically on first order)
  DECLARE @IndustryID   int = NULL; 
  DECLARE @OriginID     int = NULL; 
  DECLARE @RegionID     int = NULL; 
  DECLARE @Notes        varchar(4096) = 'Record Inserted '+convert(varchar(20), GetDate()) + ' by SQL Bridge.';
  DECLARE @Flags        varchar(4096) = '';
  DECLARE @URL          varchar(128) = '';
  DECLARE @PONumber     varchar(16) = '';
  DECLARE @PORequired   bit = 0; -- false
  DECLARE @SalespersonID int = 10; -- House Account.  Or select from Employee table where IsSalesperson = 1
  1. - Company info Pulled from Store or looked up
  DECLARE @AccountID int;
  DECLARE @CompanyNumber int;
  DECLARE @PaymentTermsID int;  
  DECLARE @TaxClassID int;  
  DECLARE @PhoneCountryCode varchar(10);
  DECLARE @JournalID              int;
  1. - Retrieve Defaults Information from databases
  SELECT  TOP 1
          @TaxClassID = coalesce(DefaultTaxClassID, 50),
          @PaymentTermsID = coalesce(DefaultPaymentTermID, 1),
          @PhoneCountryCode = coalesce(DefaultCountryCode, '1')
  FROM Store
  WHERE ID > 0
  ORDER BY ID
  1. - Company Address #1 Info
  DECLARE @StreetAddress1 varchar(256) = '12627 Jefferson Highway';
  DECLARE @StreetAddress2 varchar(256) = 'Suite C';
  DECLARE @City           varchar(256) = 'Baton Rouge';
  DECLARE @State          varchar(256) = 'LA';
  DECLARE @PostalCode     varchar(256) = '70458';
  DECLARE @AddressID              int ;
  DECLARE @AddressLinkID          int ;
  1. - Office Phone #1
  DECLARE @WorkPhoneType    int = 10;  -- Business.  From Element Table, where ClassTypeID = 4101
  DECLARE @WorkPhoneAC      varchar(10) = '225';
  DECLARE @WorkPhoneNumber  varchar(25) = '7522867';
  DECLARE @PhoneNumberID          int ;
  1. - Contact #1 Information
  DECLARE @Title varchar(25) = 'Mr.';
  DECLARE @FirstName varchar(25) = 'Handy';
  DECLARE @LastName varchar(25) = 'Bridge';
  DECLARE @Position varchar(50) = '';
  DECLARE @BirthdayMonth tinyint = NULL;
  DECLARE @BirthdayDay   tinyint = NULL;
  DECLARE @EmailAddress  varchar(50) = '';
  DECLARE @IsPrimary     bit = 1; -- true.  1 and only 1 contact must be set to True
  DECLARE @IsBilling     bit = 1; -- true.  1 and only 1 contact must be set to True
  DECLARE @ContactID              int;
  1. - Contact Additional Phone #1
  DECLARE @CellPhoneType    int = 12;  -- Mobile.  From Element Table, where ClassTypeID = 4101
  DECLARE @CellPhoneAC      varchar(10) = '225';
  DECLARE @CellPhoneNumber  varchar(25) = '202-1122';
  ;
  1. - Define some error variables in case we need them
  DECLARE @ErrorMessage    VARCHAR(2048);
  DECLARE @ErrorNumber     INT;
  DECLARE @ErrorSeverity   INT;
  DECLARE @ErrorState      INT;
  DECLARE @ErrorLine       INT;
  DECLARE @ErrorProcedure  VARCHAR(200);
  DECLARE @DT              datetime = GetDate();
  DECLARE @NewLine         char(2) =  CHAR(10)+CHAR(13);

– Step 2, Lock any necessary records

–

  1. - We can skip this for new records that don't have “parent” root records to lock
  1. - BEGIN try
  1. - EXEC dbo.csf_chapi_lock , ;
  1. - END try
  1. - BEGIN catch
  1. - SET @ErrorMessage = 'Unable to lock record. '+CONVERT(VARCHAR(16), );
  1. - – Assign variables to error-handling functions that
  1. - – capture information for RAISERROR.
  1. - SELECT
  1. - @ErrorNumber = ERROR_NUMBER(),
  1. - @ErrorSeverity = ERROR_SEVERITY(),
  1. - @ErrorState = ERROR_STATE(),
  1. - @ErrorLine = ERROR_LINE(),
  1. - @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
  1. -
  1. - – Build the message string that will contain original
  1. - – error information.
  1. - SELECT @ErrorMessage =
  1. - N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
  1. - 'Message: '+ ERROR_MESSAGE();
  1. -
  1. - – Raise an error: msg_str parameter of RAISERROR will contain
  1. - – the original error information.
  1. - RAISERROR
  1. - (
  1. - @ErrorMessage,
  1. - @ErrorSeverity,
  1. - 1,
  1. - @ErrorNumber, – parameter: original error number.
  1. - @ErrorSeverity, – parameter: original error severity.
  1. - @ErrorState, – parameter: original error state.
  1. - @ErrorProcedure, – parameter: original error procedure name.
  1. - @ErrorLine – parameter: original error line number.
  1. - );
  1. - END catch;

– Step 3, Request New IDs 

  SET @AccountID              = (SELECT dbo.csf_chapi_nextid( 2000, 1));
  SET @AddressID              = (SELECT dbo.csf_chapi_nextid( 4001, 1));
  SET @AddressLinkID          = (SELECT dbo.csf_chapi_nextid( 4002, 3)); -- Billing and Shipping for Company and Contact
  SET @PhoneNumberID          = (SELECT dbo.csf_chapi_nextid( 4100, 3)); -- Work for Company and Contact, Mobile for Contact
  SET @JournalID              = (SELECT dbo.csf_chapi_nextid( 20510, 1)); -- Company Activity
  SET @ContactID              = (SELECT dbo.csf_chapi_nextid( 3000, 1));
  SET @CompanyNumber          = (SELECT dbo.csf_chapi_nextnumber( 'CompanyNumber', 1));

– Step 4, Insert the new Records (in a transaction)

BEGIN TRANSACTION 

 BEGIN TRY
  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'     -- ,
      , @@ServerName     -- ,
      , GetDate()    -- ,
      , 0    -- ,
      , 0    -- ,
      , 1    -- ,
      , @Notes  -- 
      , @CompanyName  -- 
      , @CompanyNumber  -- 
      , NULL  -- 
      , NULL  -- 
      , @DT  -- 
      , NULL  -- 
      , NULL  -- 
      , @ContactID  -- 
      , @ContactID  -- 
      , @AddressID  -- 
      , @AddressID  -- 
      , @PhoneNumberID  -- 
      , NULL  -- 
      , @Flags  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , @TaxClassID  -- 
      , @URL  -- 
      , @IsProspect  -- 
      , 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  -- 
      , ''  -- 
      , @IsClient  -- 
      , 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     -- ,
      , 2000     -- ,
      , 'SQLBridge'     -- ,
      , @@ServerName     -- ,
      , GetDate()    -- ,
      , 0    -- ,
      , 0    -- ,
      , 1    -- ,
      );
  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'     -- ,
      , @@ServerName     -- ,
      , GetDate()    -- ,
      , 0    -- ,
      , 0    -- ,
      , 1    -- ,
      , NULL  -- 
      , @FirstName  -- 
      , @LastName  -- 
      , @Title  -- 
      , @Position  -- 
      , @EmailAddress  -- 
      , @PhoneNumberID+1  -- 
      , @PhoneNumberID+2  -- 
      , @AccountID  -- 
      , @IsPrimary  -- 
      , @IsBilling  -- 
      , @AddressID  -- 
      , @AddressID  -- 
      , @BirthdayMonth  -- 
      , @BirthdayDay  -- 
      , 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) -- 
      , '('+@CellPhoneAC+') '+@CellPhoneNumber  -- 
      , @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     -- ,
      , 3000     -- ,
      , 'SQLBridge'     -- ,
      , @@ServerName     -- ,
      , GetDate()    -- ,
      , 0    -- ,
      , 0    -- ,
      , 1    -- ,
      );
  1. - Insert Company Address
      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
      ( @AddressID   -- 
      , -1           -- ,
      , 4001         -- ,
      , 'SQLBridge'  -- ,
      , @@ServerName -- ,
      , GetDate()    -- ,
      , 0            -- ,
      , 0            -- ,
      , 1            -- ,
      , @StreetAddress1   -- 
      , @StreetAddress2   -- 
      , @City   -- 
      , @State   -- 
      , ''   -- 
      , @PostalCode   -- 
      , 'US'   -- 
      , @StreetAddress1  + @NewLine
         + (case when len(coalesce(@StreetAddress2, '')) > 1 then @StreetAddress2 + @NewLine else '' end) 
         + @City + ', ' + @State + '  ' + @PostalCode -- 
      , NULL   -- 
      , 0   -- 
      , NULL   -- 
      , 0   -- 
      , NULL   -- 
      );
  1. - Insert Company Address Links (Billing, then 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 + 0   -- 
      , -1           -- ,
      , 4002         -- ,
      , 'SQLBridge'  -- ,
      , @@ServerName -- ,
      , GetDate()    -- ,
      , 0            -- ,
      , 0            -- ,
      , 1            -- ,
      , 1   -- 
      , @AccountID   -- 
      , 2000   -- 
      , 10   -- Billing Address -- 
      , @AddressID   -- 
      , 'Billing'   -- 
      , 0   -- 
      , NULL   -- 
      , 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 + 1   -- 
      , -1           -- ,
      , 4002         -- ,
      , 'SQLBridge'  -- ,
      , @@ServerName -- ,
      , GetDate()    -- ,
      , 0            -- ,
      , 0            -- ,
      , 1            -- ,
      , 0   -- 
      , @AccountID   -- 
      , 2000   -- 
      , 11   -- Shipping Address -- 
      , @AddressID   -- 
      , 'Shipping'   -- 
      , 0   -- 
      , NULL   -- 
      , NULL   -- 
      );
  1. - Insert Company Phone Numbers
      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           -- ,
      , 4100         -- ,
      , 'SQLBridge'  -- ,
      , @@ServerName -- ,
      , GetDate()    -- ,
      , 0            -- ,
      , 0            -- ,
      , 1            -- ,
      , @AccountID   -- 
      , -1     -- 
      , 2000   -- 
      , @WorkPhoneType   -- 
      , '1'  -- 
      , @WorkPhoneAC   -- 
      , @WorkPhoneNumber   -- 
      , ''   -- 
      , '('+@WorkPhoneAC+') '+@WorkPhoneNumber   -- 
      , 0   -- 
      , (Select ElementName from Element where ClassTypeID = 4101 and ID = @WorkPhoneType) -- 
      );
  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 + 2   -- 
      , -1           -- ,
      , 4002         -- ,
      , 'SQLBridge'  -- ,
      , @@ServerName -- ,
      , GetDate()    -- ,
      , 0            -- ,
      , 0            -- ,
      , 1            -- ,
      , 0   -- 
      , @ContactID   -- 
      , 3000   -- 
      , 11   -- Shipping Address -- 
      , @AddressID   -- 
      , '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'  -- ,
      , @@ServerName -- ,
      , GetDate()    -- ,
      , 0            -- ,
      , 0            -- ,
      , 1            -- ,
      , @ContactID   -- 
      , -1     -- 
      , 3000   -- 
      , @WorkPhoneType   -- 
      , '1'  -- 
      , @WorkPhoneAC   -- 
      , @WorkPhoneNumber   -- 
      , ''   -- 
      , '('+@WorkPhoneAC+') '+@WorkPhoneNumber   -- 
      , 0   -- 
      , (Select ElementName from Element where ClassTypeID = 4101 and ID = @WorkPhoneType) -- 
      );
      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'  -- ,
      , @@ServerName -- ,
      , GetDate()    -- ,
      , 0            -- ,
      , 0            -- ,
      , 1            -- ,
      , @ContactID   -- 
      , -1     -- 
      , 3000   -- 
      , @CellPhoneType   -- 
      , '1'  -- 
      , @CellPhoneAC   -- 
      , @CellPhoneNumber   -- 
      , ''   -- 
      , '('+@CellPhoneAC+') '+@CellPhoneNumber   -- 
      , 0   -- 
      , (Select ElementName from Element where ClassTypeID = 4101 and ID = @CellPhoneType) -- 
      );
  1. - Insert Company Note into Journal
      INSERT INTO [Journal] ([ID], [StoreID], [ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EmployeeID],[JournalActivityType] ,[JournalActivityText] ,[Description] ,[Notes] ,[StartDateTime] ,[EndDateTime] ,[TotalTime] ,[ScheduledDateTime]  ,[CompletedByID] ,[CompletedDateTime] ,[IsSummary] ,[IsDetail] ,[SummaryID] ,[SummaryClassTypeID] ,[SummaryAmount] ,[DetailAmount] ,[StartGLGroupID]  ,[EndGLGroupID]  ,[AccountID] ,[AccountClassTypeID]  ,[ContactID]  ,[ContactClassTypeID]  ,[TransactionID] ,[TransactionClassTypeID]  ,[IsVoided]  ,[VoidedDateTime]  ,[VoidedEntryID] ,[VoidedEntryClassTypeID]  ,[VoidedReason]  ,[QueryStartDateTime] ,[QueryEndDateTime]  ,[ReminderDateTime]  ,[ReminderPrompt]  ,[PartID]  ,[ActivityType]  ,[ActivityTypeText]  ,[IsBillable] ,[BillableDateTime]  ,[UseActualTime]  ,[BillingNotes]  ,[BillingType]  ,[TotalBilledTime]  ,[RecurringActivityID]  ,[LinkID]  ,[LinkStoreID] ,[LinkClassTypeID] ,[SpecialCode] ,[DivisionID] ,[HasCalendarLinks] ,[TipRecipientID] ,[PartClassTypeID] ,[RecurringClassTypeID] ,[StationID] ,[StationClassTypeID] ,[CurrentState] ,[StageID] ,[StageClassTypeID]) 
      VALUES
          ( @JournalID     -- (
          , -1     -- ,
          , 20510     -- ,
          , 'SQLBridge'     -- ,
          , @@ServerName     -- ,
          , GetDate()    -- ,
          ,  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. - Now commit the Transaction
      COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
      ROLLBACK TRANSACTION;
      SELECT @ErrorMessage = ERROR_MESSAGE(),
             @ErrorSeverity = ERROR_SEVERITY(),
             @ErrorState = ERROR_STATE();
  1. - Use RAISERROR inside the CATCH block to return
  1. - error information about the original error that
  1. - caused execution to jump to the CATCH block.
      RAISERROR (@ErrorMessage, -- Message text.
                 @ErrorSeverity, -- Severity.
                 @ErrorState -- State.
                 );
  END CATCH;

– Step 5, Refresh the Customer Record

EXEC dbo.csf_chapi_refresh @AccountId, 2000, 0;

code

Source

Contributor: Cyrious Software

Date: 1/2014

Version: Control 5.1

See Also