UFN_REVENUETRANSACTION_GETACKNOWLEDGESTATUS

CREATE function dbo.UFN_REVENUETRANSACTION_GETACKNOWLEDGESTATUS
(
    @TRANSACTIONID uniqueidentifier
)
returns nvarchar(50)
with execute as caller
as
begin
    declare @STATUS nvarchar(50);

    if exists(select top 1 ID from dbo.REVENUE_EXT (nolock) where ID = @TRANSACTIONID and DONOTACKNOWLEDGE = 1)
        set @STATUS = 'Do not send acknowledgements'
    else if exists(
         select top 1 REVENUELETTER.ID
         from dbo.REVENUELETTER (nolock)
         where REVENUEID = @TRANSACTIONID and ACKNOWLEDGEDATE is not null)
         or exists(
         select top 1 RTL.ID
         from dbo.REVENUETRIBUTELETTER (nolock) RTL
         inner join REVENUETRIBUTE (nolock) RT on RTL.REVENUETRIBUTEID = RT.ID
         where RT.REVENUEID = @TRANSACTIONID and RTL.ACKNOWLEDGEDATE is not null)
         set @STATUS = 'Acknowledged';
    else
        set @STATUS = 'Not acknowledged';

    return @STATUS;
end