UFN_CONSTITUENT_GETCOMMITMENTS

            CREATE function dbo.UFN_CONSTITUENT_GETCOMMITMENTS
            (
                @CONSTITUENTID uniqueidentifier
            )
            returns nvarchar(1000)
            with execute as caller
            as
            begin
                declare @LIST nvarchar(1000);
                declare @SEPERATOR nvarchar(2);

                declare @HASPLEDGE bit; 
                declare @HASPLANNEDGIFT bit; 
                declare @HASMGPLEDGE bit;
                declare @HASRECURRINGGIFT bit;
                declare @HASEVENT bit;
                declare @HASMEMBERSHIP bit;
                declare @HASDONORCHALLENGE bit;
                declare @HASGRANTAWARD bit;


                select
                    @HASPLEDGE = 0,
                    @HASPLANNEDGIFT = 0,
                    @HASMGPLEDGE = 0,
                    @HASRECURRINGGIFT = 0,
                    @HASEVENT = 0,
                    @HASMEMBERSHIP = 0,
                    @HASDONORCHALLENGE = 0,
                    @HASGRANTAWARD = 0

                set @LIST = N'';

                /* PDG 10.03.2006 OPTIMIZED THESE 3 SEPARATE MULTI-JOINS INTO THE SINGLE SELECT BELOW
                select
                    @HASPLEDGE = case when count(REVENUE.ID) = 0 then 0 else 1 end
                from dbo.REVENUE                 
                where REVENUE.CONSTITUENTID = @CONSTITUENTID
                      and REVENUE.TRANSACTIONTYPECODE = 1
                      and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0;

                select
                    @HASMGPLEDGE = case when count(REVENUE.ID) = 0 then 0 else 1 end
                from dbo.REVENUE                 
                where REVENUE.CONSTITUENTID = @CONSTITUENTID
                      and REVENUE.TRANSACTIONTYPECODE = 3
                      and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0;

                select
                    @HASRECURRINGGIFT = case when count(REVENUE.ID) = 0 then 0 else 1 end
                from dbo.REVENUE                
                inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
                where REVENUE.CONSTITUENTID = @CONSTITUENTID
                      and REVENUE.TRANSACTIONTYPECODE = 2
                      and REVENUESCHEDULE.STATUSCODE = 0
                      and REVENUE.TRANSACTIONAMOUNT > 0;
                */

                --PDG 10.03.2006 BEGIN                

                select 
                @HASPLEDGE = sum(
                case 
                when REVENUE.TYPECODE = 1 and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0 AND coalesce(RDS.ISPENDING,0) = 0 then 1 else 0 end
                ),

                @HASPLANNEDGIFT =  sum(
                case 
                when REVENUE.TYPECODE = 4 and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0 AND coalesce(RDS.ISPENDING,0) = 0 then 1 else 0 end
                ),

                @HASMGPLEDGE = sum(
                case 
                when REVENUE.TYPECODE = 3 and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0 AND coalesce(RDS.ISPENDING,0) = 0  then 1 else 0 end
                ),

                @HASRECURRINGGIFT = sum(
                case 
                when REVENUE.TYPECODE = 2 and REVENUE.TRANSACTIONAMOUNT > 0 AND RDS.STATUSCODE in (0,5) AND coalesce(RDS.ISPENDING,0) = 0  then 1 else 0 end
                ),

                @HASDONORCHALLENGE = sum(
                case 
                when REVENUE.TYPECODE = 8 and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0 AND coalesce(RDS.ISPENDING,0) = 0  then 1 else 0 end
                ),

                @HASGRANTAWARD = sum(
                case 
                when REVENUE.TYPECODE = 6 and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0 AND coalesce(RDS.ISPENDING,0) = 0  then 1 else 0 end
                )

                from dbo.FINANCIALTRANSACTION REVENUE                  
                    left outer join dbo.REVENUESCHEDULE AS RDS on RDS.ID = REVENUE.ID 
                where REVENUE.CONSTITUENTID = @CONSTITUENTID 
                AND REVENUE.TYPECODE IN (1,2,3,4,6,8);
                --PDG 10.03.2006 END


                select
                    @HASEVENT = case when count(REGISTRANT.ID) = 0 then 0 else 1 end
                from dbo.REGISTRANT
                where REGISTRANT.CONSTITUENTID = @CONSTITUENTID
                    and dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) > 0

                --select

                --    @HASMEMBERSHIP = case when count(MEMBER.ID) = 0 then 0 else 1 end

                --from dbo.MEMBER

                --where MEMBER.CONSTITUENTID = @CONSTITUENTID;

                    --and MEMBER.ISDROPPED = 0;


                set @SEPERATOR = '';

                if @HASPLEDGE = 1
                begin
                    set @LIST = @LIST + @SEPERATOR + 'Pledge' ;
                    set @SEPERATOR = '; ';
                end

                if @HASPLANNEDGIFT = 1
                begin
                    set @LIST = @LIST + @SEPERATOR + 'Planned Gift' ;
                    set @SEPERATOR = '; ';
                end

                if @HASMGPLEDGE = 1
                begin
                    set @LIST = @LIST + @SEPERATOR + 'Matching Gift Claim';
                    set @SEPERATOR = '; '; 
                end

                if @HASRECURRINGGIFT = 1
                begin
                    set @LIST = @LIST + @SEPERATOR + 'Recurring Gift';
                    set @SEPERATOR = '; ';
                end

                if @HASEVENT = 1
                begin
                    set @LIST = @LIST + @SEPERATOR + 'Event';
                    set @SEPERATOR = '; ';
                end

                --if @HASMEMBERSHIP = 1

                --begin

                --    set @LIST =@LIST + @SEPERATOR +  'Membership';

                --    set @SEPERATOR = '; ';

                --end


                if @HASDONORCHALLENGE = 1
                begin
                    set @LIST = @LIST + @SEPERATOR + 'Donor Challenge Claim';
                    set @SEPERATOR = '; '; 
                end

                if @HASGRANTAWARD = 1
                begin
                    set @LIST = @LIST + @SEPERATOR + 'Grant Award';
                    set @SEPERATOR = '; '; 
                end

                return @LIST;
            end