Last modified by Megan Kahl on 2023/05/22 23:50
Hide last authors
Megan Kahl 5.3 1 (% style="text-align: center;" %)
2
Megan Kahl 2.1 3
Megan Kahl 5.3 4 (% style="text-align: center;" %)
Megan Kahl 6.1 5 (% style="color:#2980b9; font-size:28px" %)**SETTING UP SMS CONFIG TO GENERATE IN A STORE PROC**
Megan Kahl 2.1 6
7
8
Megan Kahl 5.3 9 (% style="text-align: center;" %)
10 (% style="color:#2980b9" %)**BY**
Megan Kahl 2.1 11
12
13
Megan Kahl 5.3 14 (% style="text-align:center" %)
15 [[image:Creating SMS Config to Generate in a Stored Proc_html_8cba33b1e8c9fa24.gif||height="128" width="463"]]
Megan Kahl 2.1 16
Megan Kahl 6.1 17 {{info}}
Megan Kahl 7.1 18 (% style="break-before: page; text-align: center;" %)
Megan Kahl 6.1 19 (% style="color:#2980b9; font-size:22px" %)**TABLE OF CONTENTS**
Megan Kahl 2.1 20
21
Megan Kahl 6.1 22 {{toc/}}
Megan Kahl 2.1 23
Megan Kahl 6.1 24
25 {{/info}}
26
Megan Kahl 8.1 27 (% style="break-before: page; text-align: center;" %)
Megan Kahl 5.3 28 |**Date**|**Version**|**Description**
29 |24/10/2016|1|Creating an SMS
Megan Kahl 2.1 30
Megan Kahl 7.1 31 (% style="break-before: page; text-align: center;" %)
Megan Kahl 5.3 32 == (% style="color:#2980b9" %)**How to Setup SMS Notifications**(%%) ==
Megan Kahl 2.1 33
34
Megan Kahl 6.1 35 === (% style="color:#2980b9" %)**1. Define at what step the SMS will be sent and create the message name in the following tables:**(%%) ===
Megan Kahl 2.1 36
37
Megan Kahl 5.3 38 (% style="color:#3498db" %)DECLARE(%%) @pkiProcessID (% style="color:#3498db" %)INT(%%), @pkiSMSConfigID (% style="color:#3498db" %)INT
Megan Kahl 2.1 39
Megan Kahl 5.3 40 (% style="color:#3498db" %)IF(%%) NOT EXISTS((% style="color:#3498db" %)SELECT * FROM(%%) tblSMSProcess (% style="color:#3498db" %)WHERE(%%) txtSPName = (% style="color:#e74c3c" %)'btr_tmpEFT_SendSMS'(%%))
Megan Kahl 2.1 41
Megan Kahl 5.3 42 (% style="color:#27ae60" %)~-~- THIS NEEDS TO BE THE NAME OF THE SP GENERATING THE SMS. This can be a description instead – used as a reference.
Megan Kahl 2.1 43
44
Megan Kahl 5.3 45 (% style="color:#3498db" %)INSERT INTO(%%) tblSMSProcess(txtDescription, txtSPName, fkiLastChangePersonID, dteLastChange)
Megan Kahl 2.1 46
Megan Kahl 6.1 47 (% style="color:#3498db" %)SELECT(% style="color:#e74c3c" %) 'Payment Generated', 'btr_tmpEFT_SendSMS'(%%), ((% style="color:#3498db" %)SELECT (%%)pkiPersonID (% style="color:#3498db" %)FROM(%%) tblPeople (% style="color:#3498db" %)WHERE(%%) txtUsername = (% style="color:#e74c3c" %)'brianr'(%%)), (% style="color:#9b59b6" %)GETDATE(%%)()
Megan Kahl 2.1 48
Megan Kahl 5.3 49 (% style="color:#27ae60" %)~-~- This is the name given for reference to the SMS type
Megan Kahl 2.1 50
51
Megan Kahl 5.3 52 (% style="color:#3498db" %)SELECT (%%)@pkiProcessID = pkiSMSProcessID (% style="color:#3498db" %)FROM (%%)tblSMSProcess (% style="color:#3498db" %)WHERE(%%) txtSPName = 'btr_tmpEFT_SendSMS'
Megan Kahl 2.1 53
Megan Kahl 6.1 54
Megan Kahl 5.3 55 (% style="color:#3498db" %)IF (% style="color:#95a5a6" %)NOT EXISTS(% style="color:#3498db" %)(SELECT * FROM(%%) tblSMSConfig (% style="color:#3498db" %)WHERE(%%) fkiSMSProcessID = @pkiProcessID)
Megan Kahl 2.1 56
Megan Kahl 5.3 57 (% style="color:#3498db" %)INSERT INTO(%%) tblSMSConfig(fkiSMSProcessID, txtShortDescr, blnError)
Megan Kahl 2.1 58
Megan Kahl 5.3 59 (% style="color:#3498db" %)SELECT(%%) @pkiProcessID, (% style="color:#e74c3c" %)'Payment Generated'(%%), 0
Megan Kahl 2.1 60
61
Megan Kahl 5.3 62 (% style="color:#3498db" %)SELECT(%%) @pkiSMSConfigID = pkiSMSConfigID (% style="color:#3498db" %)FROM(%%) tblSMSConfig (% style="color:#3498db" %)WHERE (%%)fkiSMSProcessID = @pkiProcessID
Megan Kahl 2.1 63
Megan Kahl 6.1 64
Megan Kahl 5.3 65 (% style="color:#27ae60" %)~-~- This will create the Config details for the message to be linked to
Megan Kahl 2.1 66
67
Megan Kahl 6.1 68 (% style="color:#3498db" %)IF(%%) (% style="color:#95a5a6" %)NOT EXISTS(%%)((% style="color:#3498db" %)SELECT * FROM(%%) tblSMSMessage (% style="color:#3498db" %)WHERE(%%) fkiSMSConfigID = @pkiSMSConfigID)
Megan Kahl 2.1 69
Megan Kahl 6.1 70 (% style="color:#3498db" %)INSERT INTO(%%) tblSMSMessage(fkiSMSConfigID, fkiLanguageID, txtSMSMessage)
Megan Kahl 2.1 71
Megan Kahl 6.1 72 (% style="color:#3498db" %)SELECT(%%) @pkiSMSConfigID, 1, (% style="color:#e74c3c" %)'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.'
Megan Kahl 2.1 73
Megan Kahl 6.1 74
Megan Kahl 5.3 75 (% style="color:#27ae60" %)~-~- For all the field names that can be used, look in sp_PopulateMembersSMS
Megan Kahl 2.1 76
Megan Kahl 5.3 77
Megan Kahl 6.1 78 (% style="color:#3498db" %)IF(%%) (% style="color:#95a5a6" %)NOT EXISTS(%%)((% style="color:#3498db" %)SELECT * FROM(%%) tblSystemParameters WHERE txtParameter = (% style="color:#e74c3c" %)'Exit Claim: Payment Processed'(%%))
Megan Kahl 2.1 79
Megan Kahl 6.1 80 (% style="color:#3498db" %)INSERT INTO(%%) tblSystemParameters(txtParameter, txtValue)
Megan Kahl 2.1 81
Megan Kahl 6.1 82 (% style="color:#3498db" %)SELECT(%%) (% style="color:#e74c3c" %)'Exit Claim: Payment Processed'(%%), (% style="color:#e74c3c" %)'Yes'
Megan Kahl 2.1 83
Megan Kahl 6.1 84 (% style="color:#3498db" %)GO
Megan Kahl 2.1 85
Megan Kahl 5.3 86 (% style="color:#27ae60" %)~-~- 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.
Megan Kahl 2.1 87
88
89 [[image:Creating SMS Config to Generate in a Stored Proc_html_acdab448b94a7f9a.gif]] ~-~-- Attached is the above stored proc example in query format
90
91
Megan Kahl 6.1 92 === (% style="color:#2980b9" %)**2. Create the stored Procedure that one wants to communicate on**(%%) ===
Megan Kahl 5.3 93
Megan Kahl 2.1 94
Megan Kahl 6.1 95 (% style="color:#2980b9" %)It will require the following structure:
Megan Kahl 2.1 96
Megan Kahl 6.1 97 (% style="color:#3498db" %)CREATE TABLE(%%) #SMSMessageInfo (fkiEFTID (% style="color:#3498db" %)int(%%), fkiMemberID (% style="color:#3498db" %)int(%%))
Megan Kahl 2.1 98
Megan Kahl 6.1 99 (% style="color:#3498db" %)SELECT(%%) *, (% style="color:#9b59b6" %)CAST(%%)((% style="color:#95a5a6" %)NULL(%%) (% style="color:#3498db" %)AS INT(%%)) fkiPersonID,(% style="color:#9b59b6" %)CAST(%%)((% style="color:#95a5a6" %)NULL(%%) (% style="color:#3498db" %)AS INT(%%)) fkiPersonPayingID (% style="color:#3498db" %)INTO(%%) #tmpReports (% style="color:#3498db" %)FROM(%%) tmpReports
Megan Kahl 2.1 100
Megan Kahl 6.1 101 (% style="color:#3498db" %)EXEC (%%)dbo.CreateField (% style="color:#e74c3c" %)'#tmpReports', 'txtKeyWordRef', 'varchar(255)'
Megan Kahl 2.1 102
Megan Kahl 6.1 103 (% style="color:#3498db" %)EXEC (%%)dbo.CreateField(% style="color:#e74c3c" %) '#tmpReports', 'dteMaxCategory', 'datetime'
Megan Kahl 2.1 104
Megan Kahl 6.1 105 (% style="color:#3498db" %)EXEC(%%) dbo.CreateField (% style="color:#e74c3c" %)'#tmpReports', 'fkiMemberDependentID', 'int'
Megan Kahl 2.1 106
Megan Kahl 6.1 107
Megan Kahl 5.3 108 (% style="color:#27ae60" %)~-~-- This is for the new SMS process to go through the config settings
Megan Kahl 2.1 109
Megan Kahl 6.1 110 (% style="color:#3498db" %)DECLARE(%%) @blnAutoSMS (% style="color:#3498db" %)BIT(%%), @fkiRecordID (% style="color:#3498db" %)int
Megan Kahl 2.1 111
Megan Kahl 6.1 112 (% style="color:#3498db" %)SET(%%) @fkiRecordID = 100
Megan Kahl 2.1 113
Megan Kahl 6.1 114 (% style="color:#3498db" %)EXEC(%%) @blnAutoSMS = fn_SystemParameter_Bit @txtParameter =(% style="color:#e74c3c" %) 'Exit Claim: Payment Processed'
Megan Kahl 2.1 115
116
Megan Kahl 6.1 117 (% style="color:#3498db" %)IF (%%)@blnAutoSMS = 1
Megan Kahl 2.1 118
Megan Kahl 6.1 119 (% style="color:#3498db" %)BEGIN
Megan Kahl 2.1 120
121
Megan Kahl 6.1 122 (% style="color:#3498db" %)SELECT * INTO(%%) #SMS (% style="color:#3498db" %)FROM(%%) fn_tmpSMS()
Megan Kahl 2.1 123
Megan Kahl 6.1 124 (% style="color:#3498db" %)TRUNCATE TABLE(%%) #SMS
Megan Kahl 2.1 125
Megan Kahl 6.1 126 (% style="color:#3498db" %)INSERT INTO(%%) #tmpReports(fkiRecordID, fkiMemberID, dteEffectiveDate, txtDescription)
Megan Kahl 2.1 127
Megan Kahl 6.1 128 (% style="color:#3498db" %)SELECT(%%) @fkiRecordID, fkiMemberID, (% style="color:#9b59b6" %)GETDATE(%%)(),''
Megan Kahl 2.1 129
Megan Kahl 6.1 130 (% style="color:#3498db" %)FROM(%%) #SMSMessageInfo
Megan Kahl 2.1 131
Megan Kahl 6.1 132 (% style="color:#3498db" %)EXEC(%%) [dbo].[sp_PopulateMembersSMS] @fkiRecordID, (% style="color:#e74c3c" %)'EFT PAYMENT'(%%), @fkiUserID
Megan Kahl 2.1 133
134
Megan Kahl 5.3 135 (% style="color:#27ae60" %)~-~-- For Testing, insert my cell phone number
Megan Kahl 2.1 136
Megan Kahl 5.3 137 (% style="color:#27ae60" %)~-~-_ UPDATE #SMS SET CellPhone = '0823387109'
Megan Kahl 2.1 138
139
Megan Kahl 6.1 140 (% style="color:#3498db" %)EXEC(%%) sp_SMSOutput @txtSPName=(% style="color:#e74c3c" %)'btr_tmpEFT_SendSMS'(%%), @txtBatchRef= (% style="color:#e74c3c" %)'EFT PAYMENT'(%%), @blnError=0, @fkiUserID=@fkiUserID
Megan Kahl 2.1 141
Megan Kahl 6.1 142 (% style="color:#3498db" %)END
Megan Kahl 2.1 143
144
Megan Kahl 6.1 145 === (% style="color:#2980b9" %)**Important items:**(%%) ===
146
Megan Kahl 2.1 147 1. It must have #tmpReports with all the fields that the sp_PopulateMembersSMS uses.
148 1. You can update the Cellphone to be your SMS for testing purposes.
149
Megan Kahl 7.1 150 (% style="break-before: page; text-align: center;" %)
Megan Kahl 5.3 151 == (% style="color:#2980b9" %)**Disclaimer and Copyright**(%%) ==
Megan Kahl 2.1 152
Megan Kahl 5.3 153
Megan Kahl 6.1 154 (% id="cke_bm_124S" style="color:#2980b9; display:none" %)** **(% style="color:#2980b9" %)**DISCLAIMER**
Megan Kahl 5.3 155
156 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.
157
158 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.
159
160 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.
161
162 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.
163
164
165
166 (% style="color:#2980b9" %)**COPYRIGHT**
167
168 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.
169
170
171 EBSphere (Pty) Limited
172
173 P.O. Box 785803
174
175 Sandton
176
177 2146
178
179
180
181 (% style="text-align:center" %)
182 [[image:Creating SMS Config to Generate in a Stored Proc_html_8cba33b1e8c9fa24.gif||height="128" width="463"]]

Applications

Navigation

Need help?

If you need help with XWiki you can contact: