Last modified by Megan Kahl on 2023/05/22 23:50
Show last authors
1 (% style="text-align: center;" %)
2
3
4 (% style="text-align: center;" %)
5 (% style="color:#2980b9; font-size:28px" %)**SETTING UP SMS CONFIG TO GENERATE IN A STORE PROC**
6
7
8
9 (% style="text-align: center;" %)
10 (% style="color:#2980b9" %)**BY**
11
12
13
14 (% style="text-align:center" %)
15 [[image:Creating SMS Config to Generate in a Stored Proc_html_8cba33b1e8c9fa24.gif||height="128" width="463"]]
16
17 {{info}}
18 (% style="break-before: page; text-align: center;" %)
19 (% style="color:#2980b9; font-size:22px" %)**TABLE OF CONTENTS**
20
21
22 {{toc/}}
23
24
25 {{/info}}
26
27 (% style="break-before: page; text-align: center;" %)
28 |**Date**|**Version**|**Description**
29 |24/10/2016|1|Creating an SMS
30
31 (% style="break-before: page; text-align: center;" %)
32 == (% style="color:#2980b9" %)**How to Setup SMS Notifications**(%%) ==
33
34
35 === (% style="color:#2980b9" %)**1. Define at what step the SMS will be sent and create the message name in the following tables:**(%%) ===
36
37
38 (% style="color:#3498db" %)DECLARE(%%) @pkiProcessID (% style="color:#3498db" %)INT(%%), @pkiSMSConfigID (% style="color:#3498db" %)INT
39
40 (% style="color:#3498db" %)IF(%%) NOT EXISTS((% style="color:#3498db" %)SELECT * FROM(%%) tblSMSProcess (% style="color:#3498db" %)WHERE(%%) txtSPName = (% style="color:#e74c3c" %)'btr_tmpEFT_SendSMS'(%%))
41
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.
43
44
45 (% style="color:#3498db" %)INSERT INTO(%%) tblSMSProcess(txtDescription, txtSPName, fkiLastChangePersonID, dteLastChange)
46
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(%%)()
48
49 (% style="color:#27ae60" %)~-~- This is the name given for reference to the SMS type
50
51
52 (% style="color:#3498db" %)SELECT (%%)@pkiProcessID = pkiSMSProcessID (% style="color:#3498db" %)FROM (%%)tblSMSProcess (% style="color:#3498db" %)WHERE(%%) txtSPName = 'btr_tmpEFT_SendSMS'
53
54
55 (% style="color:#3498db" %)IF (% style="color:#95a5a6" %)NOT EXISTS(% style="color:#3498db" %)(SELECT * FROM(%%) tblSMSConfig (% style="color:#3498db" %)WHERE(%%) fkiSMSProcessID = @pkiProcessID)
56
57 (% style="color:#3498db" %)INSERT INTO(%%) tblSMSConfig(fkiSMSProcessID, txtShortDescr, blnError)
58
59 (% style="color:#3498db" %)SELECT(%%) @pkiProcessID, (% style="color:#e74c3c" %)'Payment Generated'(%%), 0
60
61
62 (% style="color:#3498db" %)SELECT(%%) @pkiSMSConfigID = pkiSMSConfigID (% style="color:#3498db" %)FROM(%%) tblSMSConfig (% style="color:#3498db" %)WHERE (%%)fkiSMSProcessID = @pkiProcessID
63
64
65 (% style="color:#27ae60" %)~-~- This will create the Config details for the message to be linked to
66
67
68 (% style="color:#3498db" %)IF(%%) (% style="color:#95a5a6" %)NOT EXISTS(%%)((% style="color:#3498db" %)SELECT * FROM(%%) tblSMSMessage (% style="color:#3498db" %)WHERE(%%) fkiSMSConfigID = @pkiSMSConfigID)
69
70 (% style="color:#3498db" %)INSERT INTO(%%) tblSMSMessage(fkiSMSConfigID, fkiLanguageID, txtSMSMessage)
71
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.'
73
74
75 (% style="color:#27ae60" %)~-~- For all the field names that can be used, look in sp_PopulateMembersSMS
76
77
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'(%%))
79
80 (% style="color:#3498db" %)INSERT INTO(%%) tblSystemParameters(txtParameter, txtValue)
81
82 (% style="color:#3498db" %)SELECT(%%) (% style="color:#e74c3c" %)'Exit Claim: Payment Processed'(%%), (% style="color:#e74c3c" %)'Yes'
83
84 (% style="color:#3498db" %)GO
85
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.
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
92 === (% style="color:#2980b9" %)**2. Create the stored Procedure that one wants to communicate on**(%%) ===
93
94
95 (% style="color:#2980b9" %)It will require the following structure:
96
97 (% style="color:#3498db" %)CREATE TABLE(%%) #SMSMessageInfo (fkiEFTID (% style="color:#3498db" %)int(%%), fkiMemberID (% style="color:#3498db" %)int(%%))
98
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
100
101 (% style="color:#3498db" %)EXEC (%%)dbo.CreateField (% style="color:#e74c3c" %)'#tmpReports', 'txtKeyWordRef', 'varchar(255)'
102
103 (% style="color:#3498db" %)EXEC (%%)dbo.CreateField(% style="color:#e74c3c" %) '#tmpReports', 'dteMaxCategory', 'datetime'
104
105 (% style="color:#3498db" %)EXEC(%%) dbo.CreateField (% style="color:#e74c3c" %)'#tmpReports', 'fkiMemberDependentID', 'int'
106
107
108 (% style="color:#27ae60" %)~-~-- This is for the new SMS process to go through the config settings
109
110 (% style="color:#3498db" %)DECLARE(%%) @blnAutoSMS (% style="color:#3498db" %)BIT(%%), @fkiRecordID (% style="color:#3498db" %)int
111
112 (% style="color:#3498db" %)SET(%%) @fkiRecordID = 100
113
114 (% style="color:#3498db" %)EXEC(%%) @blnAutoSMS = fn_SystemParameter_Bit @txtParameter =(% style="color:#e74c3c" %) 'Exit Claim: Payment Processed'
115
116
117 (% style="color:#3498db" %)IF (%%)@blnAutoSMS = 1
118
119 (% style="color:#3498db" %)BEGIN
120
121
122 (% style="color:#3498db" %)SELECT * INTO(%%) #SMS (% style="color:#3498db" %)FROM(%%) fn_tmpSMS()
123
124 (% style="color:#3498db" %)TRUNCATE TABLE(%%) #SMS
125
126 (% style="color:#3498db" %)INSERT INTO(%%) #tmpReports(fkiRecordID, fkiMemberID, dteEffectiveDate, txtDescription)
127
128 (% style="color:#3498db" %)SELECT(%%) @fkiRecordID, fkiMemberID, (% style="color:#9b59b6" %)GETDATE(%%)(),''
129
130 (% style="color:#3498db" %)FROM(%%) #SMSMessageInfo
131
132 (% style="color:#3498db" %)EXEC(%%) [dbo].[sp_PopulateMembersSMS] @fkiRecordID, (% style="color:#e74c3c" %)'EFT PAYMENT'(%%), @fkiUserID
133
134
135 (% style="color:#27ae60" %)~-~-- For Testing, insert my cell phone number
136
137 (% style="color:#27ae60" %)~-~-_ UPDATE #SMS SET CellPhone = '0823387109'
138
139
140 (% style="color:#3498db" %)EXEC(%%) sp_SMSOutput @txtSPName=(% style="color:#e74c3c" %)'btr_tmpEFT_SendSMS'(%%), @txtBatchRef= (% style="color:#e74c3c" %)'EFT PAYMENT'(%%), @blnError=0, @fkiUserID=@fkiUserID
141
142 (% style="color:#3498db" %)END
143
144
145 === (% style="color:#2980b9" %)**Important items:**(%%) ===
146
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
150 (% style="break-before: page; text-align: center;" %)
151 == (% style="color:#2980b9" %)**Disclaimer and Copyright**(%%) ==
152
153
154 (% id="cke_bm_124S" style="color:#2980b9; display:none" %)** **(% style="color:#2980b9" %)**DISCLAIMER**
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: