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

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

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.

-- 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
    -- 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
 
    -- 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;
 
    -- 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
 
    -- 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 ;
 
    -- 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 ;
    -- 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;
    -- 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';
    ;
 
    -- 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
--
    -- We can skip this for new records that don't have "parent" root records to lock
    -- BEGIN try
    --     EXEC dbo.csf_chapi_lock , ;
    -- END try
    -- BEGIN catch
    --     SET @ErrorMessage = 'Unable to lock record. '+CONVERT(VARCHAR(16), );
    --     -- Assign variables to error-handling functions that
    --     -- capture information for RAISERROR.
    --     SELECT
    --         @ErrorNumber = ERROR_NUMBER(),
    --         @ErrorSeverity = ERROR_SEVERITY(),
    --         @ErrorState = ERROR_STATE(),
    --         @ErrorLine = ERROR_LINE(),
    --         @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
    --
    --     -- Build the message string that will contain original
    --     -- error information.
    --     SELECT @ErrorMessage =
    --         N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
    --             'Message: '+ ERROR_MESSAGE();
    --
    --     -- Raise an error: msg_str parameter of RAISERROR will contain
    --     -- the original error information.
    --     RAISERROR
    --         (
    --         @ErrorMessage,
    --         @ErrorSeverity,
    --         1,
    --         @ErrorNumber,    -- parameter: original error number.
    --         @ErrorSeverity,  -- parameter: original error severity.
    --         @ErrorState,     -- parameter: original error state.
    --         @ErrorProcedure, -- parameter: original error procedure name.
    --         @ErrorLine       -- parameter: original error line number.
    --         );
    -- 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
 
        -- 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  --
        );
        -- 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    -- ,
        );
 
        -- 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  --
        );
 
        -- 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    -- ,
        );
 
        -- 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   --
        );
 
        -- 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   --
        );
        -- 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) --
        );
 
        -- 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   --
        );
        -- 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) --
        );
        -- 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  -- )
        );
 
        -- Now commit the Transaction
        COMMIT TRANSACTION
 
    END TRY
 
    BEGIN CATCH
        ROLLBACK TRANSACTION;
 
        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();
 
        -- Use RAISERROR inside the CATCH block to return
        -- error information about the original error that
        -- 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;

Contributor: Cyrious Software

Date: 1/2014

Version: Control 5.1

You could leave a comment if you were logged in.