UFN_REVENUE_GETLETTERS_2

            CREATE function dbo.UFN_REVENUE_GETLETTERS_2
            (
                @ID uniqueidentifier
            )
            returns @RESULT table
            (
                ID uniqueidentifier,
                TYPE nvarchar(20),
                LETTERCODE nvarchar(100),
                PROCESSDATE datetime,
                ACKNOWLEDGEDATE datetime,
                ACKNOWLEDGEE nvarchar(154),
                TYPECODE tinyint,
                OUTOFDATE bit
            )
            as
            begin
                insert into @RESULT 

                select 
                    REVENUELETTER.ID as [ID],
                    'Revenue' as TYPE,
                    LETTERCODE.NAME,
                    REVENUELETTER.PROCESSDATE,
                    REVENUELETTER.ACKNOWLEDGEDATE,
                    CONSTITUENT.NAME as ACKNOWLEDGEE,
                    0 as TYPECODE,
                    REVENUELETTER.OUTOFDATE
                from 
                    dbo.REVENUELETTER
                  inner join dbo.LETTERCODE on REVENUELETTER.LETTERCODEID = LETTERCODE.ID
                  inner join dbo.CONSTITUENT on REVENUELETTER.ACKNOWLEDGEEID = CONSTITUENT.ID
                where 
                    REVENUELETTER.REVENUEID = @ID                     
              union all                
                select 
                    REVENUETRIBUTELETTER.ID as [ID],
                    'Tribute' as TYPE,
                    TRIBUTELETTERCODE.NAME,
                    REVENUETRIBUTELETTER.PROCESSDATE,
                    REVENUETRIBUTELETTER.ACKNOWLEDGEDATE,
                    CONSTITUENT.NAME as ACKNOWLEDGEE,
                    1 as TYPECODE,
                    null
                from 
                    dbo.REVENUETRIBUTELETTER
                  inner join dbo.TRIBUTELETTERCODE on REVENUETRIBUTELETTER.TRIBUTELETTERCODEID = TRIBUTELETTERCODE.ID
                  inner join dbo.REVENUETRIBUTE on REVENUETRIBUTELETTER.REVENUETRIBUTEID = REVENUETRIBUTE.ID
                  inner join dbo.CONSTITUENT on REVENUETRIBUTELETTER.CONSTITUENTID = CONSTITUENT.ID
                where 
                    REVENUETRIBUTE.REVENUEID = @ID
        union all
        select
          REVENUELETTER.ID,
          'Revenue',
          isnull(EMAILLETTER.NAME, MAILLETTER.NAME),
          REVENUELETTER.PROCESSDATE,
                  REVENUELETTER.ACKNOWLEDGEDATE,
                    CONSTITUENT.NAME as ACKNOWLEDGEE,
                    2 as TYPECODE,
                    REVENUELETTER.OUTOFDATE
                from 
                    dbo.REVENUELETTER
                  inner join dbo.MKTPACKAGE on REVENUELETTER.MKTPACKAGEID = MKTPACKAGE.ID
          inner join dbo.CONSTITUENT on REVENUELETTER.ACKNOWLEDGEEID = CONSTITUENT.ID
                  left join dbo.COMMUNICATIONLETTER as MAILLETTER on MAILLETTER.MAILPACKAGEID = MKTPACKAGE.ID
          left join dbo.COMMUNICATIONLETTER as EMAILLETTER on EMAILLETTER.EMAILPACKAGEID = MKTPACKAGE.ID                    
                where 
                    REVENUELETTER.REVENUEID = @ID
                order by
                    TYPE, NAME;

            return;
        end