UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS

            CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS
            (
            @APPUSERID uniqueidentifier,
            @BUSINESSPROCESSID uniqueidentifier
            )
            returns TABLE as

            /*
            Returns a row for every constituent that the the user has rights to according to record access security.


            This function is optimized for use from the Blackbaud.AppFx.Security.Catalog.ConstitRecordSecurityService
            class which implements the RecordSecurity service for Constituent record security.

            As such, it assumes that a check for DENY occurs outside this function
            and also assumes that a check for if the user is ISSYSADMIN occurs outside 
            this function.  

            It also assumes a check for UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONRACROLE 
            occurs outside this function. If that function returns true there is no need to join to this TVF.

            */

            RETURN

            (                    
                select 
                    CSAA.CONSTITUENTID AS ID 
                from 
                    dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT as CSAA
                WHERE
                    CSAA.CONSTIT_SECURITY_ATTRIBUTEID IN 
                        (
                                select 
                                    SYSTEMROLEAPPUSERCONSTITUENTSECURITY.CONSTITUENTSECURITYATTRIBUTEID
                                from 
                                    dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BUSINESSPROCESS as SV
                                    inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SV.SYSTEMROLEID
                                    inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY on SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID
                                where
                                    SV.APPUSERID = @APPUSERID and
                                    SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID and
                                    SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODECODE = 2 and
                                    SV.BUSINESSPROCESSCATALOGID = @BUSINESSPROCESSID and
                                    SV.GRANTORDENY = 1
                            )                    
                UNION ALL
                --Constits with no security attributes if the user in a role with security mode = 1

                select 
                    ID 
                from 
                    dbo.CONSTITUENT
                WHERE 
                    EXISTS(select 
                                1
                            from 
                                dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BUSINESSPROCESS as SV
                                inner join dbo.SYSTEMROLE SR on SV.SYSTEMROLEID = SR.ID                                        
                            where
                                SV.APPUSERID = @APPUSERID AND 
                                SV.BUSINESSPROCESSCATALOGID = @BUSINESSPROCESSID AND 
                                SV.GRANTORDENY = 1 AND 
                                SV.RECORDSECURITYMODE = 1
                    ) AND                
                    ID NOT IN(select 
                                    CSAA.CONSTITUENTID 
                                from
                                    dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT as CSAA
                            )
            )