USP_DATALIST_REVENUETRANSACTION_DETAIL_WITHNAMEDRECOGNITION

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

                  declare @ALLOWDELETE bit;
                  select @ALLOWDELETE = case when count(*) > 1 then 1 else 0 end
                  from dbo.FINANCIALTRANSACTION
                  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                  where FINANCIALTRANSACTION.ID = @TRANSACTIONID
                    and FINANCIALTRANSACTION.DELETEDON is null;

                  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(FINANCIALTRANSACTIONLINEITEM.ID, 1) as GROSSAMOUNT,
                    dbo.UFN_REVENUE_ISPOSTED(FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID) ISPOSTED,
                    @ALLOWDELETE ALLOWDELETE,
                    FINANCIALTRANSACTION.TYPECODE,
                    FINANCIALTRANSACTION.ID REVENUEID,
                    isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID),
                    FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
                    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    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 
                    null,
                    NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.REVENUESPLITID as PARENTID,
                    NAMINGOPPORTUNITY.NAME,
                    null,
                    NAMINGOPPORTUNITYRECOGNITION.AMOUNT,
                    null,
                    null,
                    null,
                    null,
                    null,
                    NAMINGOPPORTUNITYRECOGNITION.BASECURRENCYID,
                    null,
                    null,
                    NAMINGOPPORTUNITYRECOGNITION.ID as RECOGNITIONID,
                    NAMINGOPPORTUNITY.ID as NAMINGOPPORTUNITYID,
                    NAMINGOPPORTUNITYRECOGNITION.CONSTITUENTID,
                    NAMINGOPPORTUNITYRECOGNITION.CONSTITUENTNAME as RECOGNITIONNAME,
                    NAMINGOPPORTUNITYRECOGNITION.CONSTITUENTNAMEFORMATID as RECOGNITIONNAMEFORMATID,
                    NAMINGOPPORTUNITYRECOGNITION.CUSTOMNAMEFORMAT as RECOGNITIONCUSTOMNAMEFORMAT,
                    case when NAMINGOPPORTUNITYRECOGNITION.STARTDATE = '00000000' then '' else NAMINGOPPORTUNITYRECOGNITION.STARTDATE end as [RECOGNITIONDATE],
                      case when NAMINGOPPORTUNITYRECOGNITION.ENDDATE = '00000000' then '' else NAMINGOPPORTUNITYRECOGNITION.ENDDATE end as [RECOGNITIONENDSDATE],
                        NAMINGOPPORTUNITYRECOGNITION.QUANTITY as [QUANTITY],
                        NAMINGOPPORTUNITYRECOGNITION.INSCRIPTION as [INSCRIPTION],
                        NAMINGOPPORTUNITYRECOGNITION.SPECIALREQUEST as [SPECIALREQUEST],
                    NAMINGOPPORTUNITYRECOGNITION.ID
                    from dbo.NAMINGOPPORTUNITYRECOGNITION
                    inner join dbo.NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT on NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.NAMINGOPPORTUNITYRECOGNITIONID = NAMINGOPPORTUNITYRECOGNITION.ID
                    inner join dbo.NAMINGOPPORTUNITY on NAMINGOPPORTUNITY.ID = NAMINGOPPORTUNITYRECOGNITION.NAMINGOPPORTUNITYID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @TRANSACTIONID
                    and REVENUESPLIT_EXT.APPLICATIONCODE <> 10 --Order

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

                  order by NAME asc