USP_WORKFLOWALERT_ISSUED_ALERT_SEND

create procedure dbo.USP_WORKFLOWALERT_ISSUED_ALERT_SEND
(
    @WORKFLOWALERTID uniqueidentifier
) 
as 
begin

    declare @ALERTTYPEID uniqueidentifier = '146ac6ac-ada5-4a7a-b670-31feff8dd43f';

    if not exists (select top(1) ID from dbo.DATABASEMAILSETTINGS where ENABLED = 1)  
         return 0;

    declare @EMAILBODYCONTEXTLINKPAGEID uniqueidentifier
    declare @EMAILSUBJECT nvarchar(255);  
    declare @EMAILBODY nvarchar(max);  
    declare @EMAILVIEWFORMINSTANCEID uniqueidentifier;  
    declare @BODYLINK nvarchar(2048);  

    select  
        @EMAILSUBJECT = EMAILSUBJECT,
        @EMAILBODY = EMAILBODY,
        @EMAILBODYCONTEXTLINKPAGEID = EMAILBODYCONTEXTLINKPAGEID,
        @EMAILVIEWFORMINSTANCEID = EMAILVIEWFORMINSTANCEID
    from  
        dbo.ALERTTYPE  
    where  
        ID = @ALERTTYPEID;  

    begin try  
        if @EMAILVIEWFORMINSTANCEID is not null  
            exec dbo.USP_ALERT_PERFORMFIELDSUBSTITUTION @EMAILBODY output, @EMAILSUBJECT output, @EMAILVIEWFORMINSTANCEID, @WORKFLOWALERTID, null;  
    end try  
    begin catch  
        -- do nothing, field substitution failing shouldn't prevent email from being sent  

    end catch  

    if @EMAILBODYCONTEXTLINKPAGEID is not null  
        set @BODYLINK = dbo.UFN_ALERT_GETAPPLICATIONLINK(@EMAILBODYCONTEXTLINKPAGEID, @WORKFLOWALERTID);

    set @EMAILSUBJECT = replace(@EMAILSUBJECT, '<<APPLICATIONLINK>>', coalesce(@BODYLINK, ''));
    set @EMAILBODY = replace(@EMAILBODY, '<<APPLICATIONLINK>>', coalesce(@BODYLINK, ''))

    declare @EMAILADDRESSES table (APPUSERID uniqueidentifier, EMAILADDRESS nvarchar(100))

    insert into @EMAILADDRESSES
        select BBWORKFLOWINBOXASSIGNMENT.APPUSERID, APPUSERALERTSETTING.EMAILADDRESS from dbo.BBWORKFLOWALERT
            inner join dbo.BBWORKFLOWINBOXASSIGNMENT on BBWORKFLOWALERT.BBWORKFLOWINBOXID = BBWORKFLOWINBOXASSIGNMENT.BBWORKFLOWINBOXID
            inner join dbo.APPUSERALERTTYPESETTING on APPUSERALERTTYPESETTING.APPUSERID = BBWORKFLOWINBOXASSIGNMENT.APPUSERID
            inner join dbo.APPUSERALERTSETTING on APPUSERALERTTYPESETTING.APPUSERID = APPUSERALERTSETTING.ID
                and APPUSERALERTTYPESETTING.ALERTTYPEID = @ALERTTYPEID and APPUSERALERTTYPESETTING.ENABLED = 1
            where BBWORKFLOWALERT.ID = @WORKFLOWALERTID
        union all
        select SYSTEMROLEAPPUSER.APPUSERID, APPUSERALERTSETTING.EMAILADDRESS from dbo.BBWORKFLOWALERT
            inner join dbo.BBWORKFLOWINBOXASSIGNMENT on BBWORKFLOWALERT.BBWORKFLOWINBOXID = BBWORKFLOWINBOXASSIGNMENT.BBWORKFLOWINBOXID
            inner join SYSTEMROLEAPPUSER on BBWORKFLOWINBOXASSIGNMENT.SYSTEMROLEID = SYSTEMROLEAPPUSER.SYSTEMROLEID
            inner join dbo.APPUSERALERTTYPESETTING on APPUSERALERTTYPESETTING.APPUSERID = SYSTEMROLEAPPUSER.APPUSERID
                and APPUSERALERTTYPESETTING.ALERTTYPEID = @ALERTTYPEID and APPUSERALERTTYPESETTING.ENABLED = 1
            inner join dbo.APPUSERALERTSETTING on APPUSERALERTTYPESETTING.APPUSERID = APPUSERALERTSETTING.ID
            where BBWORKFLOWALERT.ID = @WORKFLOWALERTID

    declare @APPUSERID uniqueidentifier
    declare @EMAILADDRESS nvarchar(100)

    declare emailaddress_cursor cursor for select distinct APPUSERID, EMAILADDRESS from @EMAILADDRESSES

    open emailaddress_cursor;

    fetch next from emailaddress_cursor
    into @APPUSERID, @EMAILADDRESS;

    while @@FETCH_STATUS = 0
    begin
        exec dbo.USP_ALERT_SEND @EMAILADDRESS, @EMAILSUBJECT, @EMAILBODY, 'TEXT', 'Normal';              

        fetch next from emailaddress_cursor
        into @APPUSERID, @EMAILADDRESS;
    end
    close emailaddress_cursor;
    deallocate emailaddress_cursor;

    return 0;
end