UFN_REVENUEUPDATEBATCH_GETREVENUELETTERS

CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETREVENUELETTERS(
  @REVENUEID uniqueidentifier
)
returns xml
with execute as caller
as begin

  declare @LETTERS table( 
                         LETTERTYPECODE smallint, 
                         TRIBUTEID uniqueidentifier, 
                         LETTERCODEID uniqueidentifier, 
                         ACKNOWLEDGEEID uniqueidentifier, 
                         ACKNOWLEDGEDATE datetime,
                         PROCESSDATE datetime,
                         CLEARDATES bit,
                         OUTOFDATE bit,
                         REVENUELETTERID uniqueidentifier
                         );

 --insert revenue letters

  insert into @LETTERS
  select  
          0 as LETTERTYPECODE,
          null as TRIBUTEID,
          LETTERCODEID,
          ACKNOWLEDGEEID,
          ACKNOWLEDGEDATE,
          PROCESSDATE,
          0 as CLEARDATES,
          OUTOFDATE,
          ID as REVENUELETTERID
 from 
          dbo.REVENUELETTER 
 where 
          REVENUEID = @REVENUEID and LETTERCODEID is not null

  --insert tribute letters

  insert into @LETTERS
  select  
          1 as LETTERTYPECODE,
          RT.TRIBUTEID,
          TRIBUTELETTERCODEID as LETTERCODEID,
          RL.CONSTITUENTID as ACKNOWLEDGEEID,
          ACKNOWLEDGEDATE,
          PROCESSDATE,
          0 as CLEARDATES,
          0 as OUTOFDATE,
          RL.ID as REVENUELETTERID
  from dbo.REVENUETRIBUTELETTER RL
  inner join dbo.REVENUETRIBUTE RT on RT.ID = RL.REVENUETRIBUTEID where RT.REVENUEID = @REVENUEID

  declare @RESULT xml;  

  set @RESULT =  (select  LETTERTYPECODE, 
                          TRIBUTEID,
                          LETTERCODEID, 
                          ACKNOWLEDGEEID, 
                          ACKNOWLEDGEDATE,
                          PROCESSDATE,
                          CLEARDATES,
                          OUTOFDATE,
                          REVENUELETTERID
                   from @LETTERS
                   for xml raw('ITEM'), type, elements, root('LETTERS'), binary base64)

    return @RESULT;
end