Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [FSNEPv2]
- GO
- /****** Object: StoredProcedure [dbo].[usp_NotifyUsersOfSemiAnnualCertification] Script Date: 05/12/2010 10:47:47 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER Procedure [dbo].[usp_NotifyUsersOfSemiAnnualCertification]
- AS
- DECLARE @dayInt int
- DECLARE @monthInt int
- SET @dayInt = (SELECT DATEPART(DAY, GETDATE()))
- SET @monthInt = (SELECT DATEPART(month, GETDATE()))
- IF (@dayInt = 13) AND (@monthInt = 4 OR @monthInt = 1)
- BEGIN
- DECLARE @MailList CURSOR
- SET @MailList = CURSOR FOR
- --Get all of the 1.0 FTE TimeSheet Users
- SELECT aspnet_Membership.Email, Users.FirstName + ' ' + Users.LastName as FullName
- FROM aspnet_Membership INNER JOIN
- aspnet_UsersInRoles ON aspnet_Membership.UserId = aspnet_UsersInRoles.UserId INNER JOIN
- aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId AND aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId INNER JOIN
- Users ON aspnet_Membership.UserId = Users.UserId
- WHERE (aspnet_Roles.RoleName = N'Timesheet User') AND (FTE = 1)
- OPEN @MailList
- DECLARE @Email varchar(50), @FullName varchar(100)
- FETCH NEXT FROM @MailList INTO @Email, @FullName
- WHILE (@@FETCH_STATUS = 0)
- BEGIN
- --Send emails to each matching user
- DECLARE @bodyText varchar(MAX)
- SET @bodyText = 'This email was generated by the FSNEP Online Time Record System.
- *** Please do not respond to this email address ***
- This is a reminder that your FSNEP Semi-Annual Certification record is due by the 15th of this month. Please print and then complete the attached certification and obtain the necessary signatures and dates. Send a PDF copy of the certification to your State Office Analyst by the 15th of the month.
- If you have any questions regarding this message, or about time records in general, please contact your State Office analyst:
- Corinne Gould ( cgould@ucdavis.edu ) or
- Susan Padgett ( sdpadgett@ucdavis.edu ) or
- Yolanda Cortez (yCortez@ucdavis.edu ).
- '
- EXEC msdb.dbo.sp_send_dbmail
- @recipients=@Email,
- @subject='UC-FSNEP Semi-Annual Certification Record Due',
- @body=@bodyText
- FETCH NEXT FROM @MailList INTO @Email, @FullName
- END
- CLOSE @MailList
- DEALLOCATE @MailList
- END
Add Comment
Please, Sign In to add comment