UFN_BATCHREVENUECONSTITUENTCOMMITMENTS_GETDESCRIPTION

        CREATE function dbo.UFN_BATCHREVENUECONSTITUENTCOMMITMENTS_GETDESCRIPTION(@ID nvarchar(60)) returns nvarchar(255) 
        as
        begin
            declare @SINGLEAPPLICATIONID uniqueidentifier
          declare @APPLICATIONTYPECODE int
          declare @APPLICATIONCODE tinyint
          declare @RETURN nvarchar(255)

          if len(@ID) > 0
            if len(@ID) = 3
            begin
              set @APPLICATIONCODE = cast(substring(@ID, 3, 1) as tinyint);
              set @RETURN = case @APPLICATIONCODE
                  when 0 then 'Donation'
                  when 1 then 'Other'
                  when 2 then 'Unapplied matching gift payment'
                  when 3 then 'Sponsorship additional donation'
                end;
            end
            else
            begin
              select  
                @SINGLEAPPLICATIONID = SINGLEAPPLICATIONID,
                @APPLICATIONTYPECODE = APPLICATIONTYPECODE
              from dbo.UFN_REVENUEBATCH_PARSEAPPLICATIONINFO(@ID)

              if @APPLICATIONTYPECODE = 1
              begin
                select @RETURN = 'Recurring gift for ' + CONSTITUENT_NF.NAME + coalesce(' ' + convert(varchar(10), REVENUESCHEDULE.NEXTTRANSACTIONDATE, 101), '') +
                  coalesce(' - ' + (select top 1 dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST 
                              from dbo.REVENUESPLIT 
                                inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                              where REVENUESPLIT.REVENUEID = REVENUE.ID), '') +
                  ' - ' + cast(REVENUE.TRANSACTIONAMOUNT as varchar(20)) + ' ' +  dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID)
                from dbo.REVENUE
                          left outer join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
                          cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) CONSTITUENT_NF
                where dbo.REVENUE.ID = @SINGLEAPPLICATIONID
              end
              else if @APPLICATIONTYPECODE = 2
              begin
                select @RETURN = dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID) + ' for ' + CONSTITUENT_NF.NAME + coalesce(' ' + convert(varchar(10), MEMBERSHIP.EXPIRATIONDATE, 101), '') + ' - ' + cast(MEMBERSHIPLEVELTERM.AMOUNT as varchar(20)) + ' ' + dbo.UFN_CURRENCY_GETISO(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY())
                from  dbo.MEMBERSHIP
                          inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                          cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID) CONSTITUENT_NF
                          inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                where dbo.MEMBERSHIP.ID = @SINGLEAPPLICATIONID
              end
              else if @APPLICATIONTYPECODE = 4
              begin
                select @RETURN = 'Recurring gift for ' + CONSTITUENT_NF.NAME + coalesce(' ' + convert(varchar(10), REVENUESCHEDULE.NEXTTRANSACTIONDATE, 101), '') +
                  coalesce(' - ' + (select top 1 dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST 
                              from dbo.REVENUESPLIT 
                                inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                              where REVENUESPLIT.REVENUEID = REVENUE.ID), '') +
                  ' - ' + cast(REVENUE.TRANSACTIONAMOUNT as varchar(20)) + ' ' + dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID)
                from dbo.REVENUE
                          left outer join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
                          cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) CONSTITUENT_NF
                where dbo.REVENUE.ID = @SINGLEAPPLICATIONID
              end
              else if @APPLICATIONTYPECODE = 7
              begin
                select @RETURN = [EVENT].NAME + ' registration for ' + CONSTITUENT_NF.NAME + ' ' + convert(varchar(10), [EVENT].STARTDATE, 101) + ' - ' + cast(dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as varchar(20)) + ' ' + dbo.UFN_CURRENCY_GETISO(EVENT.BASECURRENCYID)
                from dbo.REGISTRANT
                          cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) CONSTITUENT_NF
                          inner join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID
                where dbo.REGISTRANT.ID = @SINGLEAPPLICATIONID
              end
              else
              begin
                select @RETURN = case @APPLICATIONTYPECODE when 3 then 'Order for ' + CONSTITUENT_NF.NAME
                  when 5 then 'Pledge for ' + CONSTITUENT_NF.NAME
                  when 6 then 'Planned gift for ' + CONSTITUENT_NF.NAME
                  when 8 then 'Matching gift claim for ' + CONSTITUENT_NF.NAME + ' - ' + (
                                  select 
                                      C1_NF.NAME 
                                  from 
                                      dbo.REVENUEMATCHINGGIFT MG1 
                                      inner join dbo.REVENUE R1 on R1.ID = MG1.MGSOURCEREVENUEID 
                                      cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(R1.CONSTITUENTID) C1_NF
                                  where 
                                      MG1.ID = REVENUE.ID)
                  when 9 then 'Grant award from ' + CONSTITUENT_NF.NAME
                  when 10 then 'Donor challenge claim from ' + CONSTITUENT_NF.NAME
                  end + coalesce(' ' + convert(varchar(10), INSTALLMENT.DATE, 101), '') + 
                  coalesce(' - ' + (select top 1 dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST 
                              from dbo.REVENUESPLIT 
                                inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                              where REVENUESPLIT.REVENUEID = REVENUE.ID), '') +
                  coalesce(' - ' + cast(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) as varchar(20)) , ' - 0.00')  + ' ' + dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID)
                from dbo.REVENUE
                          cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) CONSTITUENT_NF
                left outer join dbo.INSTALLMENT on INSTALLMENT.ID = dbo.UFN_REVENUE_GETNEXTINSTALLMENT(REVENUE.ID)
                where dbo.REVENUE.ID = @SINGLEAPPLICATIONID
              end
            end

          return @RETURN
        end