COINS has the functionality to send a notification to an employee when the primary or extra bank accounts have been added and/or changed. The notification will be triggered if COINS OA or ESS bank details are modified.

This gives transparency to the employee of any updates to their bank information.

By default, the notification is turn off and will need to be activated.

Database Trigger

OA Reporting & BU > Workflow > Database Triggers

Database triggers can be used to start a workflow or in this case a notification as a record is created or updated.

To use a trigger firstly define the database table in which the trigger should be used.

A database trigger will take effect whenever there is a Write to the database (ie an update or a creation). The database trigger is set up against the table name and then it uses a before and after calculation to make the trigger activate.

The Before and After values should be calculated using standard COINS calculation syntax and the After calculation should call the standard method to start the appropriate workflow or notification.

Employee Bank Details are held on 2 tables:

Employee
Pr_xpay

NOTE:

By default, the Triggers are disabled as the table has an ‘x’ after the table name. To enable remove the ‘x’ and ensure the table is the correct table name.

Employee

For a notification regarding the employee primary bank account must use ‘employee’ table.

Table	Before
Employee	debug(1); $oldbnksrt=employee.RO_emp_formattedbank-sort; $oldSocac=employee.soc-ac; $oldpregironame=employee.pre-gironame; $oldtypeamt=employee.type-amt;

This has a debug enabled. This will allow the system administrator to see the calculation in the App Server Log File (in a similar way to the report log file) The Log file can be found in $BASE/var/diag and is usually name something like:

The before is looking at the below highlighted fields on the Employee Maintenance, Payment record for the ‘Primary Bank Account Details’.

Table

After

$newbnksrt=employee.RO_emp_formattedbank-sort;

$newSocac=employee.soc-ac;

$newpregironame=employee.pre-gironame;

$newtypeamt=employee.type-amt;

a=if(equal(oldbnksrt,newbnksrt),eq,0,1,0);

b=if(equal(oldSocac,newSocac),eq,0,1,0);

c=if(equal(oldpregironame,newpregironame),eq,0,1,0);

d=if(equal(oldtypeamt,newtypeamt),eq,0,1,0);

$empmail=employee.emp_email;

$empfname=employee.fname;

$empname=employee.RO_emp_fullname;

$method$("coc-rsp.setCID",employee.kco);

$coName=co_config.coc_name;

$emessagetmp=co_config.RO_stdtext^EMPACC_CHG;

$emessage2=REPLACE$(emessagetmp,'{empfname}',empfname);

$emessage1=REPLACE$(emessage2,'{empname}',empname);

$emessage=REPLACE$(emessage1,'{CoName}',CoName);

ifexec(a + b + c + d,ge,1) method('syualert.sendHTMLmail',empmail,'','Bank Account Details Change',emessage);

If one of the fields on the ‘employee’ table as indicated in the Before is modified, then COINS will report the new record and use a ‘EMPACC_CHG’ text to email the employee the notification of the change.

Extra Pay

For a notification regarding employee additional bank accounts must use ‘pr_xpay’ table

Table	Before
Pr_xpay	debug(1); $oldpxpmax=pr_xpay.pxp_max; $oldpxpsort=pr_xpay.RO_pxp_formattedpxp_sort; $oldpxpsacct=pr_xpay.pxp_sacct; $oldpxpname=pr_xpay.pxp_name; $oldpxppercent=pr_xpay.pxp_percent;

This also has a debug enabled. This will allow the system administrator to see the calculation in the App Server Log File (in a similar way to the report log file) The Log file can be found in $BASE/var/diag and is usually name something like:

The before is looking at the below highlighted fields on the Employee Maintenance, Payment record for the ‘Extra Bank Account Details’.

Table

After

$newpxpmax=pr_xpay.pxp_max;

$newpxpsort=pr_xpay.RO_pxp_formattedpxp_sort;

$newpxpsacct=pr_xpay.pxp_sacct;

$newpxpname=pr_xpay.pxp_name;

$newpxppercent=pr_xpay.pxp_percent;

a=if(equal(oldpxpmax,newpxpmax),eq,0,1,0);

b=if(equal(oldpxpsort,newpxpsort),eq,0,1,0);

c=if(equal(oldpxpsacct,newpxpsacct),eq,0,1,0);

d=if(equal(oldpxpname,newpxpname),eq,0,1,0);

e=if(equal(oldpxppercent,newpxppercent),eq,0,1,0);

$method$("coc-rsp.setCID",pr_xpay.kco);

ikco=pr_xpay.kco;

$ckno=pr_xpay.kno;

method('emp-rsp.setEmployeeQuery',"FOR EACH employee WHERE employee.kco = " + ikco + " AND employee.kno='" + ckno + "'");

$empmail=employee.emp_email;

$empfname=employee.fname;

$empname=employee.RO_emp_fullname;

$coName=co_config.coc_name;

$emessagetmp=co_config.RO_stdtext^EMPACC_XCHG;

$emessage2=REPLACE$(emessagetmp,'{empfname}',empfname);

$emessage1=REPLACE$(emessage2,'{empname}',empname);

$emessage=REPLACE$(emessage1,'{CoName}',CoName);

ifexec(a + b + c + d + e,ge,1) method('syualert.sendHTMLmail',empmail,'','Additional Bank Account Details Change',emessage);

If one of the fields on the ‘pr_xpay’ table as indicated in the Before are modified, then COINS will report the new record and use a ‘EMPACC_XCHG’ text to email the employee the notification of the change.

Global Standard Text

OA Reporting & BU > OA Reporting & BU Setup> Global Standard Text

This will hold the email text that will be used in the notification to the employee when the ‘employee’ or ‘pr_xpay’ table has been add and/or changed.

The Database Trigger - references the code to identify which email message to send to the employee.

Primary Bank Account Email

Table	Text
EMPACC_CHG	<p>Hi {empfname},</p> <p><br />As a courtesy, we want you to know that in {CoName} the Primary bank account details for {empname} has changed.</p> <p><br />If this is correct, you can disregard this email.</p> <p><br />If you did not authorise this change, please contact payroll on 07 3147 8253.</p> <p> </p> <p>Regards</p> <p>Payroll</p>

2. Extra Bank Account Email

Table	Text
EMPACC_XCHG	<p>Hi {empfname},</p> <p><br />As a courtesy, we want you to know that in {CoName} the Extra bank account details for {empname} has changed.</p> <p><br />If this is correct, you can disregard this email.</p> <p><br />If you did not authorise this change, please contact payroll on 07 3147 8253.</p> <p> </p> <p>Regards</p> <p>Payroll</p>

3. SureStart Email Examples

Employee Bank Account Change Notification