UFN_RECOGNITIONPROGRAM_GET_VALIDCONSTITUENTS

            CREATE function dbo.UFN_RECOGNITIONPROGRAM_GET_VALIDCONSTITUENTS
            (
                @PROGRAMID uniqueidentifier
            )
            returns table
            as
            return(
                select CONSTITUENT.ID
                from dbo.CONSTITUENT
                left join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
                inner join dbo.RECOGNITIONPROGRAM on RECOGNITIONPROGRAM.ID = @PROGRAMID
                where
                    -- All households are groups, if we want to exclude households from groups

                    (RECOGNITIONPROGRAM.ALLOWGROUP = 1 and RECOGNITIONPROGRAM.ALLOWHOUSEHOLD = 0 and CONSTITUENT.ISGROUP = 1 and coalesce(GROUPDATA.GROUPTYPECODE, -1) = 1)
                    or
                    (RECOGNITIONPROGRAM.ALLOWGROUP = 1 and RECOGNITIONPROGRAM.ALLOWHOUSEHOLD = 1 and CONSTITUENT.ISGROUP = 1)
                    or
                    (RECOGNITIONPROGRAM.ALLOWHOUSEHOLD = 1 and coalesce(GROUPDATA.GROUPTYPECODE, -1) = 0)
                    or
                    (RECOGNITIONPROGRAM.ALLOWINDIVIDUAL = 1 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0)
                    or
                    (RECOGNITIONPROGRAM.ALLOWORGANIZATION = 1 and CONSTITUENT.ISORGANIZATION = 1)

            )