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
EXEC sp_ImportNote @AccountID = 10458, @Description = 'Called Bill. Topic of billboards came up. ...'; ;
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;
Source
Contributor: Cyrious Software
Date: 5/2016
Version: Control 5.7+