USP_DATALIST_REVENUETRANSACTION_DETAIL_WITHRECOGNITION

        CREATE procedure dbo.USP_DATALIST_REVENUETRANSACTION_DETAIL_WITHRECOGNITION (@TRANSACTIONID uniqueidentifier)
        as 
          set nocount on;

          declare @ALLOWDELETE bit;
          select @ALLOWDELETE = case when count(*) > 1 then 1 else 0 end
          from dbo.FINANCIALTRANSACTION where ID = @TRANSACTIONID;

          select 
            FINANCIALTRANSACTIONLINEITEM.ID [REVENUESPLITID],
            null as PARENTID,
            coalesce(DESIGNATION.NAME, 'None (Earned income)') as NAME,
            case REVENUESPLIT_EXT.TYPECODE
              when 9 then REVENUESPLIT_EXT.TYPE + ' ' + lower(REVENUESPLIT_EXT.APPLICATION)
              when 17 then 
                case REVENUESPLIT_EXT.APPLICATIONCODE
                  when 3 then 'Sponsorship recurring additional gift'
                  else 'Sponsorship additional donation'
                  end
              else
                            case REVENUESPLIT_EXT.APPLICATIONCODE when 6 then
                              case when exists(select ID from dbo.PLANNEDGIFTADDITIONREVENUE where REVENUEID = FINANCIALTRANSACTION.ID) then 'Planned gift addition'
                              else REVENUESPLIT_EXT.APPLICATION end
                            when 1 then
                              case REVENUESPLIT_EXT.TYPECODE when 0 then REVENUESPLIT_EXT.APPLICATION + ' (charitable)'
                              else REVENUESPLIT_EXT.APPLICATION end
                            else REVENUESPLIT_EXT.APPLICATION
                            end
            end,
            FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
            dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(FINANCIALTRANSACTIONLINEITEM.ID, 1, 0) as GROSSAMOUNT,
            dbo.UFN_REVENUE_ISPOSTED(FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID) ISPOSTED,
            @ALLOWDELETE ALLOWDELETE,
            FINANCIALTRANSACTION.TYPECODE,
            FINANCIALTRANSACTION.ID REVENUEID,
            isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) BASECURRENCYID,
            FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
            FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
            null,
            null,
            null,
            null,
            null as EFFECTIVEDATE,
            FINANCIALTRANSACTIONLINEITEM.ID
          from dbo.FINANCIALTRANSACTIONLINEITEM
          inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
          inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
          inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
          inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
          inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
          left outer join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
          where FINANCIALTRANSACTION.ID = @TRANSACTIONID
          and REVENUESPLIT_EXT.APPLICATIONCODE <> 10 --Order

          and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
          and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1

          union all

          select 
            fn.REVENUESPLITID,
            fn.REVENUESPLITID as PARENTID,
            fn.NAME,
            null,
            fn.AMOUNT,
            fn.GROSSAMOUNT,
            null,
            null,
            null,
            null,
            fn.BASECURRENCYID,
            null,
            null,
            fn.ID as RECOGNITIONID,
            fn.CONSTITUENTID,
            fn.REVENUERECOGNITIONTYPECODEID,
            fn.RECOGNITIONTYPE,
            fn.EFFECTIVEDATE as EFFECTIVEDATE,
            fn.ID
          from dbo.UFN_REVENUE_GETRECOGNITIONS_FORREVENUE(@TRANSACTIONID) fn

          order by NAME asc, EFFECTIVEDATE asc