Operational Manual: Bulk SMS's
The automated SMS system can easily be invoked from any program in the
ITS system. It also provides feedback with respect to the success or
failure of all SMS transmissions. The rest of this manual will explain
as to how all the features work together.
Prerequisites:
- Users must ensure that the following values are defined in {GOPS-21} : (See Step 3).
- The menu option for {SNAPPA-15} must be defined with 2 parameters : (USERS-1 Block 3)
- Menu SMNT-5 is used to associate personnel to type (SBAP) and a faculty code.
- If necessary, the firewall must be configured to allow
communications with the URL and Port (for example,
www.fwxgwsa.co.za port=50000).
This software is currently designed to work with FoneWorx as the Service Provider.
If another institution ever wants to use another Service Provider,
package gga01pkg will not process correctly. A possibility here would
be to create an extra parameter which contains the name of a local
package. The standard package gga01pkg would first check for the
existence of this parameter. If it exists, the package would call the
local package instead, sending the same parameters as it had received.
The local package would have a similar framework to gga01pkg (i.e. same
procedure and function names, same input and output parameters, same
insert and update actions). Users would need to change the SMS ERR
parameters accordingly. No other programs would need to be changed, so
they could all remain standard.
Fields in this
option:
Step 1: Client/User must have an account at FoneWorx.
User must have a FoneWorx Account.
Users will receive "Service Provider Accounts" from FoneWorx, once they have registered.
The Service Provider accounts should be
set up at the Service Provider if necessary. For Foneworx, this
typically involves creating "sub-accounts" to represent departments or
faculties. The Foneworx login screen could be accessed from
http://www.foneworx.co.za/sms/cgi-bin/index.cgi.
Example:
Step 2: USERS-1 Block 3
Make sure Parameters are setup, also make sure "Parameter 1, is not NOSMS.
Query on USERS-1_SNAPPA. Select option 15, (next block). Make sure that the parameter settings are correct, if not change them.
- Parameter 1 : SMS or NOSMS to indicate whether SMS transmissions should take place for updates.
- Parameter 2 : F or D to indicate whether Service Provider accounts are based on Faculty or Department.
Example:
Step 3: GOPS-21
Check to see that all defaults are available on this option as listed below.
By default, the values shown for SMS ERR will have been inserted into GOPS-21 already. Users only need to change these if (a) they are using a service provider other than FoneWorx or (b) Foneworx change their status codes.
The exterrnal code represents the code received back from the Service
Provider, and the interrnal code represents the translated
value used by some ITS programs. When a message is initially
transmitted to a Service Provider, the status is set to 'U' and then
'S'. If the message is not sent, because an error is detected
beforehand (e.g. a cell number with alpha characters), the status
is set to 'X'. Status codes of 'S', U' and 'X' are included to
facilitate translations for consistency.
Example:
Step 4: SMNT-5
Maintain User Restrictions g2aapp.fmb
User must have been linked to "Type = SBAP" in order to have total access to "Bulk SMS"
This form now incorporates a new access type ('SBAP' for Student Bulk
Applications). The access type will prompt for a faculty code, not a
department code. A list of values for faculty codes will be available.
SCRIPT:
t166529u2.sql : Create new Access Type for Faculty Access
INSERT INTO gen.gavrtp
(gavrestc, gavrestn, gavrestd,
gavid, gavrtg, gavbpdesc1, gavvalind1, gavsubsys,
gavresacc, gavuseseq)
VALUES ('SBAP', 'BULK SELECTIONS (FACULTY', 'USERS LINKED HERE WILL
HAVE TOTAL ACCESS', 'i', NULL, 'FAC CODE', 1, 'S', 'A', 'N');
Example:
Link user to Faculty Code for access.
Step 5: GSMSAPI-1 Maintain Service Provider Account Information. ggjapp.fmb
User MUST have an account in order to setup the Service provider information.
The new menu option should be used
to setup Service Provider accounts, passwords, and relationships
between these account and either departments or faculties at the
institution.
This form will enable users to define
SMS Code/Account relationships which will be used by programs to derive
service provider account codes. The form contains a second
tabsheet which will enable new third part accounts to be created or
maintained. (The Bulk Selections form will look for relationships where
the System Code = 'i' and the Process Code = 'APPL'.) See Processng
Rules.
This program comprised of 2 data blocks :
A tabular
data block with a base table of GEN.GOPSCA where users can define SMS
Code/Account relationships. Each row will contain a System Code (e.g.
'i'), a Process Code (e.g. 'APPL'), a Sequence Number (defaulted to 1),
a Type Value (F=Faculty, D=Department, Q=Qualification, S=Subject), a
value associated to the code (e.g. an actual department number), and a
related account code . A lookup for process code on table GEN.GNRPCD
will be available.
A tabular data block with a base table
of GEN.GOOSAC where users can add, modify or delete Service Provider
accounts and passwords (see table GEN.GOOSAC ). Deletions or changes to
the Account Name will only be permissible if (a) no entries exist for
the account name in GEN.GOPSCA and (b) no entries exist for the account
name in STUD.JRBSMS where JRBSMSSTAT = 'U' for Unsent or 'R' for Resend.
The table is indexed as follows :
Index 1 (unique): GOPUGSYSTC
GOPPROCC
GOPSEQ
GOPSQIND
GOPSQCODE
iv) New Sequence GEN.GOQSEQ
Sequence for SMS Reference Numbers
This sequence will start at 1001 and will have a maximum value of 99999999.
Example:
Step 6: SNAPPA-15 Bulk Application Selections i2japp.fmb (adapted from ina271.fmb)
The menu option for SNAPPA-15 must be defined with 2 parameters.
The program will no longer create an
email to send to the Service Provider. Instead it inserts entries into
the SMS Transmissions table.
Note : a menu parameter (Parameter 2) must contain an F or a D to
indicate to the program which entity will be used for deriving Service
Provider accounts.
- As the user will see, from setting up the USERS-1b3 paramets "2"
= "F", and SMNT-5 user number is linked to "Faculty", the faculty
inserted there will default in SNAPPA-15.
Filtering options:
| Name |
Description |
| Application Year |
The application year the user wishes to run the filter on. |
| Block |
The user has an option to run for a specific "Block" |
| Campus |
A faculty can have many campuses, a user can select a certain campus in the defaulted faculty. |
| Qualification Codes |
There is provision for "5"
qualifications. A user can either select a qualification, maybe 5 or
leave it all blank in order to pick up all qualifications for the year. |
| Offering Type. |
the offering type of the qualifications. |
| Period of Study |
|
Note : a menu parameter (Parameter 2) must contain an F or a D to
indicate to the program which entity will be used for deriving Service
Provider accounts.
This form will be changed as follows :.
- ina271.fmb accesses a local UKZN audit
table to accommodate the From and To Date filter on the main screen.
The program will be changed to simply check the application date on the
standard ITS applications table, since this date is updated whenever
the status of an application is changed.
- The "Stats"
tabsheet is based on information derived from a local UKZN table
containing projected intake figures by approved qualification for the
year. The standard form will be changed to access quotas on the
standard system and compare these to actual acceptance figures.
- The form utilises a local stored procedure to derive Matric Scores. The
standard form will be changed to utilise the WRS score on the
associated application instead.
- The hard-coded
Institutional abbreviation (currently set as UKZN) will be replaced by
a lookup to the External Conversion Codes table for the abbreviation.
- Users will be able to type in up to 800 characters when sending
user-defined SMS's.Tests at UKZN have shown that the restriction of 160
characters for SMS messages no longer applies at Foneworx, although
messages longer than 160 characters will incur 2 or more "debits"
against the account.
- The program will utilise function
ggc01fun to ensure that users have access to appropriate faculty codes.
In this way, institutions who have implemented departmental access for
other programs can still use this program and control access at a
faculty level.
- Contact information such as addresses, phone
numbers and NOK names need to be derived using calls to g01pkg. See
ggd01pkg for details on how to derive the student's cell number.
This form was changed as follows :
- The program inserts rows into STUD.JRBSML. The program will be
changed to call gen.ggd01pkg.create_sms instead. The following
parameters will be supplied :
- p_unum = Student Number
- p_numtype = 'I'
- p_system_code = 'S'
- p_process_code = 'APPL'
- p_indicator = menu parameter 1
- p_indicator_value = Faculty Code if menu parameter 1 = 'F' or
Department Code if menu parameter = 'D'. For each message to be sent,
derive either the faculty code from STUD.IAIQAL.IAIFAC or the
department code from STUD.IAIQAL.IAISCHOOLDEPT .
- p_sms_message = SMS Message to be sent
- The program allows users to specify a Changed From and a Changed
To date as part of the filtering parameters. Currently, the program
access a local UKZN audit table. It will be changed to check that the
value in STUD.IERAAD.IERDATE instead.
- The existing local-software code in the Stats tabsheet will be
replaced by standard code. Only one table of data will be
displayed. Each row will represent a Qualification/Offering
Type/Period of Study and will show the Quota from STUD.IGXQQT.IGXQUOTA
and the number of accepted applications for this Qualification
/Offering Type/Period of Study. Rows will be displayed for
Qualification/Offering Type/Period of Study combinations based on
criteria on the filter screen in this form. A total will be shown below
the table for all rows.
- The existing local software code to derive Matric scores will be
replaced by using STUD.IERAAD.IERWRS as the Matric score.
- Currently, the program uses 'UKZN' where applicable for the
abbreviated institution. It will be changed to derive
STUD.IKXETR.IKXINTCODE where IKXEXTBODY = 'PAR' and IKXEXTCTYPE =
'SMD', and to use this code as the abbreviation for the institution.
- Currently, the program derives an abbreviation for UKZN campus
codes. This abbreviation is included in bulk SMS's for updates. The
program will be changed to not include this campus abbreviation in the
message.
- The program will be changed to cater for up to 800 characters for user-defined SMS's.
- The program currently uses code 'PDEP' to ascertain whether users
have access to faculty codes. It will be changed to call ggc01fun
with the specified faculty code as the parameter. If ggc01fun returns
FALSE, access will be denied.
- The program will be changed to call the standard report i2kccc.pc
instead of the local equivalent ina281.pc for printing directly from
the form.
- The program will be changed to exclude the local tabsheet which displays audit entries from local table STUD.NI_IERAAD_AUD.
- The program will be changed to use calls to gen.g01pkg when
retrieving cell numbers and other contact information including
addresses. See ggd01pkg for details on deriving cell numbers.
Example:
Step 7: Bulk Applications Selection
Processing Rules for
this Block (delete if not applicable).
Example:
Step 8: Sort Features
Processing Rules for
this Block (delete if not applicable).
Example:
Step 9: Bulk Buttons
Processing Rules for
this Block (delete if not applicable).
Example:
Step 10: Bulk Update
Processing Rules for
this Block (delete if not applicable).
Example:
Step 11: Bulk Print
Processing Rules for
this Block (delete if not applicable).
Example:
Step 12: Bulk SMS
Processing Rules for
this Block (delete if not applicable).
Example:
Example:
Example:
Example:
Example:
GSMSAPI-2:
gghccc.pc (adapted from ina273.pc)
GSMSAPI #2 Send
SMS's to Service Provider
There are 2 batch programs : one which sends messages for processing at
the Service Provider (gghccc.pc), and one which reads back delivery
status codes from the Service Provider (gggccc.pc). It is recommended
that the first program be set up to run continuously as a cron job
during the day, and that the second program be scheduled to be run at
least once per day - preferably after hours. Both jobs should provide
the word CRON as a parameter. For example :
gghccc <outputfile> <user/password> CRON
This program prompts users for a user account, and then creates and
sends emails for all SMS's which need to be sent or resent to the
Service Provider (e.g. Foneworx). Table STUD.JRBSML is accessed for
this purpose - the program processes all entries in the specified date
range where the status = 'R' for resend.
The program will be changed as follows :
- Process status 'U' entries (for unsent) as well as status 'R' entries.
- Call the Service Provider API via gga01pkg instead of sending emails (gga01pkg.sql is set up to work with FoneWorx).
- Only prompt for a user account if the 4th runtime parameter is null or
is not CRON; otherwise all accounts will be processed. When a cron
invokes this program, it will send CRON as the 4th parameter.
This program was changed as follows :
- Process accounts where one or more entries exist where JRBSMSSTAT = 'U' for Unsent or 'R' for Resend)
- Call gga01pkg to.send_message instead of sending an email for each
group of SMS's to be delivered. The program will supply one parameters
to gga01pkg - the Service Provider account. Previously, the program
sent one email per Service Provider account; it will now call
gga01pkg.send_message once per Service Provider account. The program
will derive distinct values for JRBACCOUNTNAME where JRBSMSSTAT = 'U'
or 'R' and will call gga01pkg.send_message.
- The program will
prompt for a Service Provider account. If the 4th runtime parameter =
'CRON', the program will default this parameter to 'ALL'.
GSMSAPI-3:
gggccc.pc (adapted from ina272.pc)
GSMSAPI #3
Report on undelivered SMS's and Remaining SMS Credits
This program prompts users for a start date and a user account, and
then calls package gen.gga01pkg which connects to the SMS Service
Provider to establish which SMS's were logged as sent from ITS but were
not successfully sent by the Service Provider. The program produces
this information by faculty to allow errors to be distributed
appropriately.
The program also shows available balances for user accounts at the Service Provider.
The program was changed as follows :
- The Delivery Status received back from the Service Provider will
be translated to a local value by this program using conversions
defined in GOPS #21 (for example, 3 from Foneworx will be translated to
D for delivered).
- In cases where the submission of an SMS was rejected by the
Service Provider because of problems such as illegal characters in the
message or an invalid cell number, the package will have set the Status
(now termed the Delivery Status) to 'X' and set the Submission Status
(which is a new field) to the appropriate error. If this report
encounters a Delivery Status of 'X', the Submission Status (which is an
alphanumeric description returned from the Service Provider) will also
be printed.Taken out because the report becomes cluttered with this
information (which is not very relevant). Users will be able to view
submission status's on the SMS Enquiries screen (ggiapp.fmb).
- The status code for successfully delivered messages has been
changed from 'A' to 'D'. This program need to be changed accordingly.
- A new status code of 'U' for Unsent(set up in GOPS #21) has
been introduced to indicate entries which have not been sent to the
Service Provider yet - previously, all messages were sent immediately
to the Service Provider via email, so there was no need to this status.
This program will be changed to include Unsent entries on the report.
- The existing program prompts users for a From Date and a User
Account. The program will be changed so that, if the 4th runtime
parameter = 'CRON', the program will default these values to Sysdate -
7 (one week ago) and 'ALL' respectively.
- The program will cater for SMS's of up to 800 characters long.
- The program will be ordered and grouped by Service Provider Account Name instead of Faculty.
- The program will translate delivery codes in
STUD.JRBSML.JRBSMSSTAT to internal delivery codes using STUD.IKXETR
where IKXEXTBODY = 'SMS', ikxextctype = 'ERR' and IKXEXTCODE =
JRBSMSSTAT.
- For entries where the internal delivery code = 'X',
the description from JRBSMSSUBMITSTAT will be printed on the line
below. Because of space constraints, this message will be truncated to
18 characters.
- The program checks where the internal delivery =
'A' for successfully delivered messages. This will be changed from 'A'
to 'D'.
- The program will also print details of any messages where the internal delivery status = 'U' for Unsent.
- If the 4th parameter supplied to the program = 'CRON', the program will
default the input parameters of From Date and Account to SYSDATE-7 and
ALL respectively.
- The program will cater for SMS's of up to 800 characters.
- The program will be ordered and grouped by JRBACCNAME instead of by Faculty.
Example:
t166529d1.sql
i) STUD.JRBSML
SMS Transmissions
This table needs to be changed as follows :
1. Student Number will be changed to User Number.
2. User Number Type will be added.
3. The description for column JRBSMSSTAT will be changed to Delivery Status Code.
4. The existing column JRBSMSMSG will be extended to
be 800 characters long. In tests with FoneWorx, it was found that
messages of this length are processed successfully (although credits
are used up at a rate of 1 per 160 characters).
5. A column will be added for the submission status :
JRBSMSSUBMITSTAT - Submission Statusi2kccc.pc (adapted from ina281.pc)
i2kccc.pc
(adapted from ina281.pc) Menu not
applicable Print Applicant Details
The UKZN version of the Bulk Selections form includes a "Bulk Print"
button via which users can produce a PDF document containing
biographical, application, PSE, address and other information for one
or more selected applicants. The local report which is used to produce
this PDF will be converted to a standard program so that the standard
Bulk Selections form also has this functionality.
The local program used "hard-coded" address types to derive contact information. The program will be
changed to derive contact information using calls to g01pkg - see ggd01pkg for an example of deriving a student's cell number.
The
UKZN version of the Bulk Selections form includes a "Bulk Print" button
via which users can produce a PDF document containing biographical,
application, PSE, address and other information for one or more
selected applicants. The local report which is used to produce this PDF
will be converted to a standard program so that the standard Bulk
Selections form also has this functionality.
The program will be
changed to use calls to g01pkg when retrieving cell numbers and other
contact information including addresses. See ggd01pkg for details on
how to derive a student's cell number. Other information such as postal
addresses and NOK addresses can be derived using the same SQL with
different ADRFTC and TAC values.
Apart from the renaming of the
program name within the source code and the renaming of the program
itself, no other changes should be necessary
The UKZN version of the Bulk Selections form includes a "Bulk Print"
button via which users can produce a PDF document containing
biographical, application, PSE, address and other information for one
or more selected applicants. The local report which is used to produce
this PDF will be converted to a standard program so that the standard
Bulk Selections form also has this functionality.
The program will be changed to use calls to g01pkg when retrieving cell
numbers and other contact information including addresses. See ggd01pkg
for details on how to derive a student's cell number. Other information
such as postal addresses and NOK addresses can be derived using the
same SQL with different ADRFTC and TAC values.Apart from the renaming
of the program name within the source code and
the renaming of the program itself, no other changes should be necessary
6. A column will be added to hold a unique reference number which will be derived from an Oracle Sequence.
7. A new index will be created for Reference Number matching.
Also, status A will be changed to
status D (Delivered) and status U (Unprocessed) will also be
permissible in programs accessing this table.
This
form enables users to query SMS messages which have been sent to a
service provider. Users are able to query entries by account, user,
cell number, user number (i.e. student number in this context) ,
status, message text or date sent
The form was changed as follows :
- also display the Submission Status Description and Reference Number for each entry.
- Cater for SMS's up to 800 characters long.
- Change descriptions for Delivery Status's - the descriptions will be
looked up from the descriptions define in GOPS #21 for External Body
'SMS' and External Code Type 'ERR' where the External Code matches the
status on the SMS table.
- The program will be based on user-number and user-type instead of on student number.
- A menu parameters will indicate whether users should be restricted by
Faculty or Department. Allow users of other departments/faculties to
view each others information here.
This program will be changed as follows :
- The program currently displays the full SMS text at the bottom of the
form for the entry on which the user is positioned. It will now also
display the Submission Status (JRBSML.JRBSMSSUBMITSTAT).
- The program will also display the Reference Number (JRBSMSREFNO) to users.
- The program will cater for the possibility of some messages being up to 800 characters in length.
- The program will translate delivery codes in STUD.JRBSML.JRBSMSSTAT to
internal delivery codes using STUD.IKXETR where IKXEXTBODY = 'SMS',
ikxextctype = 'ERR' and IKXEXTCODE = JRBSMSSTAT.
- The
descriptions for Delivery Status's will be derived from
STUD.IKXETR.IKXEXTDESC where IKXEXTBODY = 'SMS', IKXEXTCTYPE = 'ERR'
and IKXEXTCODE = JRBSMSSTAT.
- The form will be changed from being student-based to being user-based, using JRBNUMTYPE and JRBUNUM instead of JRBSTNO.
- The form will be based on user type and user number instead of student number.
Example:
ggkccc.pc
GSMSAPI #5
Delete Historical SMS Data
This
program will prompt users for the minimum number of days which must
have gone past for SMS's to be deleted. Regardless of how old SMS's in
the system are, the program will not delete entries with a status of
'U' (unsent) or 'R' (resend).
This program will prompt users for the following parameter :
Entries will only be deleted where they are nn days old. Specify nn :
The
program will then extract all entries in STUD.JRBSML where JRBDATESENT
is before SYSDATE - nn days and where JRBSMSSTAT is not one of 'U' or
'R'. For each entry, the program will print the following information
and then delete it from STUD.JRBSML :
Cell Number
User Number (typically the student number or staff number)
Account Name
User
Delivery Status
First 50 characters of SMS message
Processing
Rules |
|
| |
gga01pkg.sql (adapted from
ina10pkg.sql) Return logged
information from SMS Service
Provider using HTTP
This package will be enhanced to:
- include routines to send messages via an API.
- change the matching process when status codes are received
back from the Service Provider. The matching process will now use a
unique reference number (derived from an Oracle Sequence object) which
will be stored on the SMS transmission table for each entry created.
Messages retrieved back from the Service Provider should contain this
reference number at the end of each message, thereby enabling accurate
matching.
- Dispense with table JRCSWT because details received back
from the Service Provider can now be linked directly back to
JRBSML.JRBSMSREFNO.
ggb01fun.sql (adapted from ina39fun.sql)
Cell
Phone Validation
This function receives a cell number as a parameter and either returns
null if valid or an appropriate error message if invalid. No changes
are necessary.
|
Processing
Rules |
|
| |
ggc01fun.sql
Faculty Access Check
This function will check whether the user who is currently logged in
has access to the specified faculty (which will be supplied as the only
parameter).
k) i2kccc.pc (adapted from ina281.pc)
Menu not applicable
Print Applicant Details
l) ggd01pkg.sql
Insert
into SMS Transmissions Table
This package will contain a function called create_sms. The parameters
supplied to this function will be used to insert an entry into the SMS
transmissions table. Another program will use the data in the SMS
transmissions table to attempt to send the data to Foneworx for
processing
|
See Also:
History of Changes
| Date |
System Version |
By Whom |
Job |
Description |
| 13-Jun-2013 |
v02.0.0.0 |
Amanda van Emmenis |
t185654 |
New manual - refer to T166529 as done on INT1 |
