UFN_REVENUEUPDATEBATCH_GETRECOGNITIONS

CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETRECOGNITIONS(@REVENUEID uniqueidentifier)
    returns table
      as
      return
      (
      select 
        RS.ID as APPLICATIONID,
        255 as APPLICATIONTYPECODE,
        case when R.TRANSACTIONTYPECODE = 0 then (case when RS.APPLICATIONCODE = 0 and RS.TYPECODE = 0 then 0
                          when RS.APPLICATIONCODE = 4 and RS.TYPECODE = 4 then 1
                          when RS.APPLICATIONCODE = 7 and RS.TYPECODE = 0 then 2
                          when RS.APPLICATIONCODE = 0 and RS.TYPECODE = 17 then 3 end)
        else 0 end  ADDITIONALAPPLICATIONTYPECODE,
        RS.DESIGNATIONID as ADDITIONALAPPLICATIONDESIGNATIONID, 
        RS.AMOUNT as APPLICATIONAMOUNT,
        '' as APPLICATIONDESCRIPTION,
        dbo.UFN_REVENUEUPDATEBATCH_GETCOLLECTIONDESCRIPTION(RS.ID, 1) as COLLECTIONDESCRIPTION,          
        (select
            '00000000-0000-0000-0000-000000000000' as ID,
            REVENUERECOGNITIONTYPECODEID,
            CONSTITUENTID,
            EFFECTIVEDATE,
            AMOUNT,          
            ID as REVENUERECOGNITIONID,
            BASECURRENCYID,
            RECOGNITIONS.RECOGNITIONCREDITFKID,
            RECOGNITIONS.DESIGNATIONID,
            RECOGNITIONS.DONORCHALLENGERECOGNITIONTYPECODE
            from dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS(RS.ID) RECOGNITIONS 
          for xml raw('ITEM'),type,elements,BINARY BASE64
          ) as RECOGNITIONS,
          RS.BASECURRENCYID,
          coalesce(RSGA.DECLINESGIFTAID, 0) as ADDITIONALAPPLICATIONDECLINESGIFTAID,
        case when RS.APPLICATIONCODE = 0 and RS.TYPECODE = 17 then RS.ID
             else null
        end as ADDITIONALAPPLICATIONSPONSORSHIPID
      from dbo.REVENUE R
      inner join dbo.REVENUESPLIT RS on R.ID = RS.REVENUEID
    left outer join dbo.REVENUESPLITGIFTAID RSGA on RS.ID = RSGA.ID
    left outer join dbo.SPONSORSHIP S on S.REVENUESPLITID = RS.ID
      where R.ID = @REVENUEID
      )