**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 note to a customer, order, or estimate. You only need to (and are only allowed to) specify one of the following:

  • @OrderNumber - The order number
  • @EstimateNumber - The Estimate number
  • @TransHeaderID - The ID of the Order or Estimate in the TransHeader table
  • @AccountID - The ID of the company in the Account Table
  • @CompanyName - The exact text name of the company.

If you specify an order number, estimate number, or TransHeader ID then the note will be for that order or estimate. Otherwise, the note will be for the company specified.

You must also provide

  • @Description - The subject line

You may provide the following information

  • @Notes - The full body of notes for the activity
  • @ContactID - The ID of the company contact. If not provided, the primary contact for the company is used.
  • @EmployeeID - The ID of the employee for the activity
  • @EmployeeLast and @EmployeeFirst - the last and first names of the employee for the activity

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 note on a customer record.

EXEC sp_ImportNote
    @AccountID = 10458,
    @Description = 'Called Bill.  Topic of billboards came up. ...';
;

This inserts a note on an order for the employee "Dean Sharp". Notice the use of multiple lines in the @Notes field.

EXEC sp_ImportNote
    @OrderNumber   = '1174',
    @EmployeeFirst = 'Dean',
    @EmployeeLast  = 'Sharp',
    @Description   = 'Called to talk over offer.',
    @Notes = 'I called Jim.  At first I didn''t like his offer
    but the more I though about it the more comfortable I got.
    In the end, I decided we should just accept it and move on.'

Stored Procedure

The stored procedure to create these notes is:

-- =============================================
-- Author:        Cyrious Sofware
-- Create date: May-2016
-- Description:   This stored procedure creates a note in Control.
--                Many of the parameters are option, but if not supplied will used
--                The default behavior.
--
-- Returns:     New ID for Note (Journal.ID)
-- =============================================
CREATE PROCEDURE sp_ImportNote
    --
    -- All calls must supply *one* and only one of the following values
    --
    @AccountID          INT                = NULL,        -- The ID of the Account
    @CompanyName    VARCHAR(255)    = NULL,        -- The company name.  Must match EXACTLY and be the only one
    @OrderNumber     INT             = NULL,     -- The ORder Number
    @EstimateNumber INT                = NULL,        -- The Estimate Number
    @TransHeaderID    INT                = NULL,        -- The ID of the TransHeader records
    -- other fields
    @Description    VARCHAR(255),                -- REQUIRED: Subject / Short Description
    @Notes            VARCHAR(MAX)    = NULL,        -- Long Text for Note
    -- optional fields
    @ContactID        INT                = NULL,        -- if not supplied, primary contact will be used
    -- Enter the EmployeeID or the Employee's first and last name
    -- If both are entered, only the ID is used
    -- If neither are entered, it will use 10 (House Account)
    @EmployeeID          INT             = NULL,        -- Default to house account if not supplied
    @EmployeeFirst  VARCHAR(64)        = NULL,
    @EmployeeLast   VARCHAR(64)     = NULL
 AS
BEGIN
    --
    -- Step 0. Declare some variables and constants to use
    --
    -- Define the activity types from http://control.cyriouswiki.com/SQL+Structure+-+Journal+Table
    DECLARE @OrderActivityType                 SMALLINT         = 3;
    DECLARE @EstimateActivityType             SMALLINT         = 3;  -- need to confirm this
    DECLARE @CompanyActivityType             SMALLINT         = 6;
    DECLARE @OrderActivityClassTypeID         SMALLINT         = 20500;
    DECLARE @EstimateActivityClassTypeID     SMALLINT         = 20500;
    DECLARE @CompanyActivityClassTypeID     SMALLINT         = 20510;
    DECLARE @ActivityType                     INT             = NULL;
    DECLARE @ActivityClassTypeID            INT             = NULL;
    DECLARE @ActivityTypeText                 VARCHAR(25)     = NULL;
    DECLARE @TransactionType                tinyint         = NULL;
    DECLARE @DivisionID                      INT             = NULL;
    DECLARE @ValidationError                 VARCHAR(2048)     = '';
    DECLARE @DT                              SMALLDATETIME     = GetDate();
    DECLARE @ComputerName                    VARCHAR(25)     = @@ServerName;
    DECLARE @NewLine                         CHAR(2)         =  CHAR(10)+CHAR(13);
    --
    -- Step 1. Validate and fill in the input
    --
    DECLARE @IDs tinyint =
        ( SELECT
            (CASE WHEN @AccountID IS NULL THEN 0 ELSE 1 END)
            + (CASE WHEN @CompanyName IS NULL THEN 0 ELSE 1 END)
            + (CASE WHEN @OrderNumber IS NULL THEN 0 ELSE 1 END)
            + (CASE WHEN @EstimateNumber IS NULL THEN 0 ELSE 1 END)
            + (CASE WHEN @TransHeaderID IS NULL THEN 0 ELSE 1 END)
        );
    IF (@IDs = 0) SET @ValidationError = @ValidationError + 'You must supply one of the needed ID fields; ';
    IF (@IDs > 1) SET @ValidationError = @ValidationError + 'Only one ID may be supplid; ';
    IF (@CompanyName IS NOT NULL)
    BEGIN
        SET @AccountID = (SELECT ID FROM Account WHERE CompanyName = @CompanyName);
          IF (@AccountID IS NULL)
            SET @ValidationError = @ValidationError + 'Company '+@CompanyName+' not found; ';
    END;
    IF (@AccountID IS NOT NULL)
    BEGIN
        SET @ActivityType         = @CompanyActivityType;
        SET @ActivityTypeText     = 'Company';
        SET @ActivityClassTypeID= @CompanyActivityClassTypeID;
        SET @DivisionID          = (SELECT DivisionID FROM Account WHERE ID = @AccountID);
    END;
    IF (@EstimateNumber IS NOT NULL)
    BEGIN
        SET @ActivityType         = @EstimateActivityType;
        SET @ActivityTypeText     = 'Estimate';
        SET @ActivityClassTypeID= @EstimateActivityClassTypeID;
        SET @TransactionType    = 2;
        SELECT     @TransHeaderID = ID,
                @DivisionID = DivisionID
        FROM TransHeader
        WHERE EstimateNumber = @EstimateNumber
          AND TransactionType = 2;
          IF (@TransHeaderID IS NULL)
            SET @ValidationError = @ValidationError + 'Estimate '+CONVERT(VARCHAR(12), @EstimateNumber)+' not found; ';
    END;
    IF (@OrderNumber IS NOT NULL)
    BEGIN
        SET @ActivityType         = @OrderActivityType;
        SET @ActivityTypeText     = 'Order';
        SET @ActivityClassTypeID= @OrderActivityClassTypeID;
        SET @TransactionType    = 1;
        SELECT     @TransHeaderID = ID,
                @DivisionID = DivisionID
        FROM TransHeader
        WHERE OrderNumber = @OrderNumber
          AND TransactionType IN (1,6);
          IF (@TransHeaderID IS NULL)
            SET @ValidationError = @ValidationError + 'Order '+CONVERT(VARCHAR(12), @OrderNumber)+' not found; ';
    END;
    IF (@TransHeaderID IS NOT NULL)
    BEGIN
        SELECT @AccountID = AccountID,
               @TransactionType = TransactionType
        FROM TransHeader
        WHERE ID = @TransHeaderID;
        IF (@TransactionType IN (1,6))
        BEGIN
            SET @ActivityType        = @OrderActivityType;
            SET @ActivityClassTypeID= @OrderActivityClassTypeID;
            SET @ActivityTypeText    = 'Order';
        END;
        IF (@TransactionType = 2)
        BEGIN
            SET @ActivityType        = @EstimateActivityType;
            SET @ActivityClassTypeID= @EstimateActivityClassTypeID;
            SET @ActivityTypeText    = 'Estimate';
        END;
          IF (@AccountID IS NULL)
            SET @ValidationError = @ValidationError + 'Record does not exist or AccountID is NULL for Transheader.ID '+CONVERT(VARCHAR(12), @TransHeaderID)+'; ';
    END;
    IF (@EmployeeID IS NULL)
    BEGIN
          IF (@EmployeeFirst IS NULL OR @EmployeeLast IS  NULL)
            SET @EmployeeID = 10  -- Fallback to the House Account if nothing found
        ELSE
        BEGIN
              SET @EmployeeID = (SELECT TOP(1) ID FROM EMPLOYEE
                                 WHERE LastName = @EmployeeLast AND FirstName = @EmployeeFirst);
            IF (@EmployeeID IS NULL)
                SET @ValidationError = @ValidationError + 'Employee '+@EmployeeFirst+' '+@EmployeeLast+' not found; ';
        END;
    END;
    IF (@ContactID IS NULL)
        SELECT @ContactID = PrimaryContactID
        FROM Account
        WHERE ID = @AccountID;
    -- Raise an error and halt if there are any validation errors
    IF (@ValidationError  '')
    BEGIN
        DECLARE @ErrorMessage    VARCHAR(2048) = 'Input Validation Failed in Add Notes: '+@ValidationError;
        DECLARE @ErrorSeverity   INT = 16;
        DECLARE @ErrorState      INT = 1;
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState );
        RETURN;
    END;
    --
    -- Step 2. Obtain the ID from Chapi.
    --
    DECLARE @JournalID INT    = (SELECT dbo.csf_chapi_nextid( 20510, 1)); -- Company Activity
    --
    -- Step 3. Save the Note.  (No need for a transaction because just one 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     -- ,
        , @ActivityClassTypeID     -- ,
        , 'SQLBridge'     -- ,
        , @ComputerName     -- ,
        , @DT    -- ,
        ,  0    -- ,
        ,  0    -- ,
        ,  1    -- ,
        , @EmployeeID  -- 
        , @ActivityType  -- 
        , @ActivityTypeText  -- 
        , @Description  -- 
        , @Notes  -- 
        , @DT  -- 
        , @DT  -- 
        , NULL  -- 
        , NULL  -- 
        , @EmployeeID  -- 
        , @DT  -- 
        , 1  -- 
        , 1  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , @AccountID  -- 
        , 2000  -- 
        , @ContactID  -- 
        , 3000  -- 
        , @TransHeaderID  -- 
        , 10000  -- 
        , 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 4. Return the new ID
    --
    SELECT @JournalID;
END;

Contributor: Cyrious Software

Date: 5/2016

Version: Control 5.7+

You could leave a comment if you were logged in.