Configuration

NOTE: For a listing of the queries used as examples, refer to the List of Queries in the Appendix section below.

The recommended approach for correct implementation is to take the following steps:

1. Design your email template

Determine the format that your email message will take in the GNA Email Template Editor. In the Parameters Expression field, build the expression that will be used to extract the parameters needed for your email message. For example a message that is pertaining to overdue accounts might require some of the following pieces of information.
From and To fields for the email, account holder names and account numbers, dates, and so on.
Refer to the Email Template Editor Wiki page or user guide for details about using the editor.
As indicated earlier, build your query in the parameters expression, it is here that you will determine the required Govern ID’s.
For example…:
{
To = Query(‘naNameInfo’, ‘EMAIL’);
Name = Query(‘naNameInfo’, ‘FULLNAME’);
AccountNumber = Query(‘stAccountNo’, ‘ACCOUNT_NO’);
Category = Query(‘stCategoryCode’, ‘SHORT_DESC_EN’);
PeriodeCode = Query(‘stPeriodCode’, ‘SHORT_DESC_EN’);
PeriodeNumber = Query(‘stPeriodNumber’, ‘SHORT_DESC_EN’);
DueDate = Query(‘stDueDate’, ‘DUE_DATE’);
}
In the above expression, we need the NA_ID’s for name information, ST_ID’s for ST account information and due dates, and so on.

NOTE: Each required query must be created. Refer to the Appendix section below for a listing of queries.

The SELECT query is now written to retrieve the Govern ID’s that are needed in the email template.

NOTE: A message will also need to be created under the Message sub tab in order to be able to save your Email Template; see the Sample Email Message below for an example message

2. Design Your Query

The SELECT Query

It is within the SELECT query being used that you set the Govern ID’s.
Looking at the following query…:
SELECT top 100 f.st_acct_id,
f.na_id,
f.st_id
FROM st_filing_master f
INNER JOIN NA_NAMES n ON n.na_id=f.na_id
WHERE f.unfiled <> 0 AND
f.due_date < GETDATE() AND
(n.email IS NOT NULL AND n.EMAIL <> ‘ ‘)
ORDER BY f.st_acct_id, f.st_id;
The above query was designed in the Select Query Editor in GNA. It will list all filings that are late. An email will be sent to the NA_MAILING address. For the purpose of this example, only the top 100 records are selected.

NOTE: No entities or Govern ID’s are used in the above query. When it is run in the Scheduler or Govern, no ID’s will be set except for the user ID’s and the run date.

3 – Define a New Batch Process

Define a new Batch Process in the GNA Batch Process Definition Editor. This batch process will be initiated through Govern.

1. Create a new Code.
2. Complete the Short and Long Description fields.
3. in the Process parameter, select the “Send Email From Template” option
4. Click the Save icon on the Batch Process Definition tab.

TIP: Ensure that the newly defined Batch Process has been added to the required profile so that it will be accessible under the Govern Batch Processes Explorer tab.

4 – Design or Select an Email Template

As mentioned earlier, email templates are designed in the GNA Email Template Editor. In the email template parameter expressions can be used. Refer to the About Parameters Expression Field section.
For our example, an email message will be sent to all mailing addresses with a late filing. The information that is required will be retrieved by the Govern ID’s. Each record returned by the query will look through each column to see if it corresponds to a Govern ID; if this is the case, the Govern ID will be set. Then when the email is being sent via the template, the Govern ID will be passed to the template to be used directly within the email, or as a Parameter Expression that is passed to another query. This expression can be designed in the Expression selector.
In our example, another query has been designed to extract information such as the naNameInfo, EMAIL, FULLNAME, ACCOUNT_NO, and so on.

Batch Process Parameters

Selection Criteria group parameters

Looking at the batch process in Govern, the parameters are the standard that are required to be completed.
Select Query – This is the list of available SELECT queries.
Email Template – All email templates in the system.

Run the Batch Process

Once the batch process has been run, each email message will be formatted with the extracted information. All parameters have been replaced with the values requested.

Troubleshooting

Any issues that are associated with this Batch Process that fall under the heading of troubleshooting will be listed in this section.

APPENDIX

List of Queries

The following queries were used as examples for demonstration purposes. They can used as a starting point and modified for other purposes.

— Query Expression:

— To be used in the Parameters Expression field of the Email Template
{
To= Query(‘naNameInfo’,‘EMAIL’);
Name = Query(‘naNameInfo’,‘FULLNAME’);
AccountNumber = Query(‘stAccountNo’,‘ACCOUNT_NO’);
Category = Query(‘stCategoryCode’,‘SHORT_DESC_EN’);
PeriodeCode = Query(‘stPeriodCode’,‘SHORT_DESC_EN’);
PeriodeNumber = Query(‘stPeriodNumber’,‘SHORT_DESC_EN’);
DueDate = Query(‘stDueDate’,‘DUE_DATE’);
}

— Sample Email Message

The following is a sample email message that can be modified as required:

Dear @Name,
The following Account #@AccountNumber has registered a late payment for the @PeriodeCode period of @PeriodeNumber in the @Category.
The actual due date was @DueDate.

— naNameInfo

SELECT EMAIL,
CASE
WHEN FORMAT_CODE=‘c’THEN COMPANY
ELSE FIRST_NAME ||‘ ‘|| LAST_NAME
ENDas FULLNAME
FROM NA_NAMES
WHERE NA_ID=@idna_id

— stAccountNo

SELECT ACCOUNT_NO
FROM ST_MASTER
WHERE ST_ACCT_ID=@idst_acct_id

— stCategoryCode

SELECT VT_USER.SHORT_DESC_EN
FROM ST_FILING_MASTER
INNERJOIN VT_USER ON VT_USER.YEAR_ID=ST_FILING_MASTER.YEAR_ID AND
VT_USER.DEPT=ST_FILING_MASTER.DEPT AND
VT_USER.CODE=ST_FILING_MASTER.CATEGORY_CODE
WHEREST_FILING_MASTER.ST_ID=@idst_idAND
VT_USER.TABLE_NAME=‘ST_CATEG’

— stPeriodCode

SELECT (CASEWHEN ST_FILING_MASTER.PERIOD_CODE =‘ST_INACTIVE_PER’THEN‘Inactive’ELSE VT_USER.SHORT_DESC_EN END)AS SHORT_DESC_EN
FROM ST_FILING_MASTER
INNERJOIN VT_USER ON VT_USER.YEAR_ID=ST_FILING_MASTER.YEAR_ID AND
VT_USER.DEPT=ST_FILING_MASTER.DEPT AND
VT_USER.CODE=ST_FILING_MASTER.PERIOD_CODE
WHEREST_FILING_MASTER.ST_ID=@idst_idAND
VT_USER.TABLE_NAME=‘ST_PER’

— stPeriodNumber

SELECT VT_USER.SHORT_DESC_EN
FROM ST_FILING_MASTER
INNERJOIN VT_USER ON VT_USER.YEAR_ID=ST_FILING_MASTER.YEAR_ID AND
VT_USER.DEPT=ST_FILING_MASTER.DEPT AND
VT_USER.CODE=(ST_FILING_MASTER.PERIOD_CODE +‘_’+CAST(ST_FILING_MASTER.PERIOD_NB ASVARCHAR))
WHEREST_FILING_MASTER.ST_ID=@idst_idAND
VT_USER.TABLE_NAME=‘ST_PER_DETAIL’

— stDueDate

SELECT DUE_DATE
FROM ST_FILING_MASTER
WHERE st_id=@idst_id