UFN_REVENUE_GETACKNOWLEDGESTATUS

            create function dbo.UFN_REVENUE_GETACKNOWLEDGESTATUS
            (
                @REVENUEID uniqueidentifier
            )
            returns nvarchar(20)
            with execute as caller
            as
            begin
                declare @STATUS nvarchar(20);

                declare @DONOTACKNOWLEDGE bit;

                select @DONOTACKNOWLEDGE = DONOTACKNOWLEDGE from dbo.REVENUE where ID = @REVENUEID;

                if @DONOTACKNOWLEDGE = 1 
                    set @STATUS = 'Do not acknowledge';
                else if (select count(ID) from dbo.REVENUELETTER where REVENUEID = @REVENUEID and ACKNOWLEDGEDATE is null) > 0
                    or (select count(ID) from dbo.REVENUELETTER where REVENUEID = @REVENUEID) = 0
                    set @STATUS = 'Not acknowledged';
                else
                    set @STATUS = 'Acknowledged';

                return @STATUS;
            end