Add Tasks for NSFs

1. Create and configure an Task Category to be used for NSF Tasks

2. Create a client workflow rule

   1. Specify the name of the workflow

   2. Leave the SQL Select empty

   3. Enter the SQL Update - see example below and make modifications as desired

   4. Check Scheduled

   5. Select Schedule Frequency and Start

   6. Check Active

   7. Click Save

3. Make sure System Status Job "Scheduled workflow rules for clients" is enabled or enable and then modify the schedule in SSMS

The user should have prior knowledge of SQL language and understand the structure of the existing database before attempting to construct a SQL statement. All statements should be tested in SSMS before adding to the Administration Console.

Example SQL Update

DECLARE @categoryid INT, @departmentid INT, @status nvarchar(20), @categoryname nvarchar(50), @date DATETIME, @AssignedUser nvarchar (50)

SET @date = GETDATE()

SET @categoryid = 36 -- NSF Category

SELECT @categoryname = category,@departmentid = defaultdepartmentid  from Taskcategories where categoryid = @categoryid

SELECT @status = [Status] from [Status] where IsDefault = 1 and (category = @categoryname or category is null)

INSERT INTO Tasks(CreatedUserID, AssignedUserID, ASSignedDepartmentID, ClientID,

ClientType, Status, Summary, Priority,CategoryID,category, TaskDate,CreatedDate,TaskType)

SELECT 'Automation', e.UserID, @departmentid, r.ClientID, 'Client',@status,

'NSF returned on ' + convert(nvarchar,r.nsfdate,101) + ' Reason: ' + CAST(isnull(r.nsfreason,'') as nvarchar) + ' - Contact the client.',

'High',@categoryid,@categoryname,@date,@date,'Client'

FROM Receipts r

INNER JOIN Clients c on r.ClientID = c.ClientID

LEFT JOIN ezy_users e on c.Counselor = e.userid AND e.notallowtasks = 0 and Disabled = 0

WHERE NSF = 1

AND NSFDate > @LTR

AND NSFDate <= @CTR