Setting up SMS Config to Generate in a Store Proc

SETTING UP SMS CONFIG TO GENERATE IN A STORE PROC

BY

How to Setup SMS Notifications

1. Define at what step the SMS will be sent and create the message name in the following tables:

DECLARE @pkiProcessID INT, @pkiSMSConfigID INT

IF NOT EXISTS(SELECT * FROM tblSMSProcess WHERE txtSPName = 'btr_tmpEFT_SendSMS')

-- THIS NEEDS TO BE THE NAME OF THE SP GENERATING THE SMS. This can be a description instead – used as a reference.

INSERT INTO tblSMSProcess(txtDescription, txtSPName, fkiLastChangePersonID, dteLastChange)

SELECT 'Payment Generated', 'btr_tmpEFT_SendSMS', (SELECT pkiPersonID FROM tblPeople WHERE txtUsername = 'brianr'), GETDATE()

-- This is the name given for reference to the SMS type

SELECT @pkiProcessID = pkiSMSProcessID FROM tblSMSProcess WHERE txtSPName = 'btr_tmpEFT_SendSMS'

IF NOT EXISTS(SELECT * FROM tblSMSConfig WHERE fkiSMSProcessID = @pkiProcessID)

INSERT INTO tblSMSConfig(fkiSMSProcessID, txtShortDescr, blnError)

SELECT @pkiProcessID, 'Payment Generated', 0

SELECT @pkiSMSConfigID = pkiSMSConfigID FROM tblSMSConfig WHERE fkiSMSProcessID = @pkiProcessID

-- This will create the Config details for the message to be linked to

IF NOT EXISTS(SELECT * FROM tblSMSMessage WHERE fkiSMSConfigID = @pkiSMSConfigID)

INSERT INTO tblSMSMessage(fkiSMSConfigID, fkiLanguageID, txtSMSMessage)

SELECT @pkiSMSConfigID, 1, 'Your exit from the <ParentName> has been completed with payment today, per your instruction. Transfers between banks can take up to 48 hours to reflect.'

-- For all the field names that can be used, look in sp_PopulateMembersSMS

IF NOT EXISTS(SELECT * FROM tblSystemParameters WHERE txtParameter = 'Exit Claim: Payment Processed')

INSERT INTO tblSystemParameters(txtParameter, txtValue)

SELECT 'Exit Claim: Payment Processed', 'Yes'

GO

-- This will enable the SMS to be turned on and off in the system parameters. If this is linked to the new client references, then it can be limited at a fund level.

 --- Attached is the above stored proc example in query format

2. Create the stored Procedure that one wants to communicate on

It will require the following structure:

CREATE TABLE #SMSMessageInfo (fkiEFTID int, fkiMemberID int)

SELECT *, CAST(NULL AS INT) fkiPersonID,CAST(NULL AS INT) fkiPersonPayingID INTO #tmpReports FROM tmpReports

EXEC dbo.CreateField '#tmpReports', 'txtKeyWordRef', 'varchar(255)'

EXEC dbo.CreateField '#tmpReports', 'dteMaxCategory', 'datetime'

EXEC dbo.CreateField '#tmpReports', 'fkiMemberDependentID', 'int'

--- This is for the new SMS process to go through the config settings

DECLARE @blnAutoSMS BIT, @fkiRecordID int

SET @fkiRecordID = 100

EXEC @blnAutoSMS = fn_SystemParameter_Bit @txtParameter = 'Exit Claim: Payment Processed'

IF @blnAutoSMS = 1

BEGIN

SELECT * INTO #SMS FROM fn_tmpSMS()

TRUNCATE TABLE #SMS

INSERT INTO #tmpReports(fkiRecordID, fkiMemberID, dteEffectiveDate, txtDescription)

SELECT @fkiRecordID, fkiMemberID, GETDATE(),''

FROM #SMSMessageInfo

EXEC [dbo].[sp_PopulateMembersSMS] @fkiRecordID, 'EFT PAYMENT', @fkiUserID

--- For Testing, insert my cell phone number

--_ UPDATE #SMS SET CellPhone = '0823387109'

EXEC sp_SMSOutput @txtSPName='btr_tmpEFT_SendSMS', @txtBatchRef= 'EFT PAYMENT', @blnError=0, @fkiUserID=@fkiUserID

END

Important items:

1. It must have #tmpReports with all the fields that the sp_PopulateMembersSMS uses.
2. You can update the Cellphone to be your SMS for testing purposes.

Disclaimer and Copyright

 DISCLAIMER

Please note, whilst care is taken to illustrate numerous scenarios, it is impossible to detail all instances and therefore this document should merely serve as a guide for the purposes of demonstrating system functionality and conducting employee training.

In accordance with the above paragraph, this training manual is published and distributed on the basis that EBSphere (Pty) Ltd is not responsible/accountable for any actions taken by users of information contained in this training manual nor for any error in or omission from this manual.

EBSphere (Pty) Ltd does not accept any responsibility whatsoever for a misrepresentation performed by any person in respect of claims, losses or damage or any other matter, either direct or consequential arising out of or in relation to the use and reliance, whether wholly or partially, upon any information contained or products referred to in this manual.

Further, the contents of this training manual are subject to change without any notice and further highlight the need for this training manual to serve only as a guide.

COPYRIGHT

This documentation and the software described in it is the exclusive property of EBSphere (Pty) Ltd. This manual or the software may not be copied in whole or part, without the express written consent of EBSphere (Pty) Ltd. Under the law, copying includes translating into another language or format.

EBSphere (Pty) Limited

P.O. Box 785803

Sandton

2146