Tips from Clark Public Utilities on Managing the Affordable Care Act |
With the Affordable Care Act, we found that our managers needed to manage their student hours a lot more closely. We don’t offer our students health coverage, but if their hours went over a certain amount, we would get in trouble if we didn’t offer coverage. So we decided to send out bi-weekly emails to the managers with students with a listing of their students YTD hours. Sounds like a job for Enterprise Server!
First, I need a list of all the managers with students, so I created a stored procedure to provide me with that list. Next, I created another stored procedure that lists the students and their hours for a given manager. I like to use stored procedures whenever I can. If there’s a change in the logic, I can just update the stored procedure and not have to worry about updating the Enterprise Server (ES) job. Another side benefit is it makes it easy to test the job. While testing, I can put in my person id for the email in the stored procedure and all emails come to me.
Once I had these stored procedures in place, it was time to tackle Enterprise Server. This actually took two ES jobs, one job actually called the other.
Student Hours Email
This job calls the student hours Managers stored procedure and loops through the result set calling the second ES job STUDENTHOURSMGREMAIL. The BINDDATA-START and BINDDATA-END is the magic behind calling the second stored procedure for every record. The “cmd_resolution” passes the values from the stored procedure to the second ES job.
Click here to login to PDS Support and view the CPU_sp_StudentHours code.
The second job is Student Hours Managers Email. This job sends the email to the managers. This ES job is actually called by Student Hours Email. The parameter MGR_ID is received from the command resolution in the first job.
Click here to login to PDS Support and view the CPU_sp_StudentHoursManagers code.
And just like that, we have emails coming out to our managers!
Susan Shaw
Lead Application Analyst Developer
Clark Public Utilities
SShaw@clarkpud.com