USP_DATALIST_REVENUETRANSACTION_DETAIL_WITHSOLICITORS

                CREATE procedure dbo.USP_DATALIST_REVENUETRANSACTION_DETAIL_WITHSOLICITORS (@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, 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,
                        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 FINANCIALTRANSACTION.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                    and REVENUESPLIT_EXT.APPLICATIONCODE <> 10 --Order


                    union all

                    select 
                        null,
                        FINANCIALTRANSACTIONLINEITEM.ID as PARENTID,
                        CONSTITUENT.NAME,
                        null,
                        REVENUESOLICITOR.AMOUNT,
                        null,
                        null,
                        null,
                        null,
                        null,
                        isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) BASECURRENCYID,
                        null,
                        null,
                        REVENUESOLICITOR.CONSTITUENTID,
            REVENUESOLICITOR.ID
                    from dbo.REVENUESOLICITOR
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESOLICITOR.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                    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
                    inner join dbo.CONSTITUENT on REVENUESOLICITOR.CONSTITUENTID = CONSTITUENT.ID
                    where FINANCIALTRANSACTION.ID = @TRANSACTIONID
                    and FINANCIALTRANSACTION.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                    and REVENUESPLIT_EXT.APPLICATIONCODE <> 10 --Order


                    order by NAME asc