UFN_CONSTITUENT_HASCOMMITMENTS

            CREATE function dbo.UFN_CONSTITUENT_HASCOMMITMENTS
            (
                @CONSTITUENTID uniqueidentifier
            )
            returns bit
            as 
            begin

                --NOTE: There is now an inline table version of this function, UFN_REVENUE_CONSTITUENTSWITHCOMMITMENTS. Any new commitment logic 

                --needs to be added to both. 


                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

                /* JamesWill 2006-10-04 Optimized these 3 separate multi-joins into the single select below. Seem optimization in ver. 5 of UFN_CONSTITUENT_GETCOMMITMENTS
                select
                    @HASPLEDGE = case when count(REVENUE.ID) = 0 then 0 else 1 end
                from dbo.FINANCIALTRANSACTION REVENUE                 
                where REVENUE.CONSTITUENTID = @CONSTITUENTID
                      and REVENUE.TYPECODE = 1
                      and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0;

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

                if @HASPLEDGE = 0 and @HASMGPLEDGE = 0
                    select
                        @HASRECURRINGGIFT = case when count(REVENUE.ID) = 0 then 0 else 1 end
                    from dbo.FINANCIALTRANSACTION REVENUE                    
                    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
                    where REVENUE.CONSTITUENTID = @CONSTITUENTID
                          and REVENUE.TYPECODE = 2
                          and REVENUESCHEDULE.STATUSCODE  in (0,5)
                          and REVENUE.TRANSACTIONAMOUNT > 0;
                */
                --JamesWill 2006-10-04 Begin

                select
                    @HASPLEDGE = coalesce(sum(
                        case when REVENUE.TYPECODE = 1 and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0 and coalesce(REVENUESCHEDULE.ISPENDING,0) = 0 then 1 else 0 end
                    ), 0),
                    @HASPLANNEDGIFT = coalesce(sum(
                        case when REVENUE.TYPECODE = 4 and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0 and coalesce(REVENUESCHEDULE.ISPENDING,0) = 0 then 1 else 0 end
                    ), 0),
                    @HASMGPLEDGE = coalesce(sum(
                        case when REVENUE.TYPECODE = 3 and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0 and coalesce(REVENUESCHEDULE.ISPENDING,0) = 0 then 1 else 0 end
                    ), 0),
                    @HASRECURRINGGIFT = coalesce(sum(
                        case when REVENUE.TYPECODE = 2 and REVENUE.TRANSACTIONAMOUNT > 0 and REVENUESCHEDULE.STATUSCODE in (0,5) and coalesce(REVENUESCHEDULE.ISPENDING,0) = 0 then 1 else 0 end
                    ), 0),
                    @HASGRANTAWARD = coalesce(sum(
                        case when REVENUE.TYPECODE = 6 and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0 and coalesce(REVENUESCHEDULE.ISPENDING,0) = 0 then 1 else 0 end
                    ), 0),
                    --JamesWill 2011-06-17 Move donor challenge stuff into the super select to avoid having to run it every time. 

                    @HASDONORCHALLENGE = coalesce(sum(
                        case when REVENUE.TYPECODE = 8 and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0 then 1 else 0 end
                    ), 0)
                from dbo.FINANCIALTRANSACTION REVENUE                
                left join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
                where REVENUE.CONSTITUENTID = @CONSTITUENTID
                and REVENUE.TYPECODE in (1, 2, 3, 4, 6, 8)
                --JamesWill 2006-10-04 End


                if @HASPLEDGE = 0 and @HASPLANNEDGIFT = 0 and @HASMGPLEDGE = 0 and @HASRECURRINGGIFT = 0 and @HASGRANTAWARD = 0 and @HASDONORCHALLENGE = 0
                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

                --if @HASPLEDGE = 0 and @HASPLANNEDGIFT = 0 and @HASMGPLEDGE = 0 and @HASRECURRINGGIFT = 0 and @HASEVENT = 0 and @HASGRANTAWARD = 0 and @HASDONORCHALLENGE = 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


                if 1 in (@HASPLEDGE,
                         @HASPLANNEDGIFT, 
                         @HASMGPLEDGE, 
                         @HASRECURRINGGIFT,
                         @HASEVENT,
                         @HASMEMBERSHIP,
                         @HASDONORCHALLENGE,
                         @HASGRANTAWARD)
                    return 1;

                return 0;
            end