UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE_BULK

            CREATE function [dbo].[UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE_BULK]
            (
                @APPUSERID uniqueidentifier,
                @SMARTQUERYCATALOGID uniqueidentifier
            )
            returns table as

            /*
            Returns a row for every revenue record the given user has permissions to
            in a role whose SITE is either

            1.) Blank and record security mode=1
            2.) Assigned to a SITE that exists on the given Revenue.

            This function is optimized for use from within a smart query
            which respects the constituent security policy that has been configured.

            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_SMARTQUERY_IN_NONSITEROLE
            occurs outside this function. If that function returns true there is no need to join to this TVF.

            */

            return (
                select distinct
                    ID as REVENUEID
                from
                dbo.UFN_SITEID_MAPFROM_REVENUEID_BULK() as REVENUESITEMAP
                inner join
                (
                    --Find all sites the user has access to specifically

                    select distinct
                        SITEPERMISSION.SITEID
                    from 
                        dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY as SECURITYVIEW
                        inner join dbo.SITEPERMISSION on SITEPERMISSION.APPUSERID = SECURITYVIEW.APPUSERID and SITEPERMISSION.SYSTEMROLEID = SECURITYVIEW.SYSTEMROLEID
                    where
                            SECURITYVIEW.APPUSERID = @APPUSERID and
                            SECURITYVIEW.SMARTQUERYCATALOGID = @SMARTQUERYCATALOGID and
                            SECURITYVIEW.GRANTORDENY = 1 and
                            SECURITYVIEW.SITESECURITYMODE in (2,3)
                    union
                    --Check if the user has permissions to records with no site

                    select
                        null
                    from 
                        dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY as SECURITYVIEW
                    where
                            SECURITYVIEW.APPUSERID = @APPUSERID and
                            SECURITYVIEW.SMARTQUERYCATALOGID = @SMARTQUERYCATALOGID and
                            SECURITYVIEW.GRANTORDENY = 1 and
                            SECURITYVIEW.SITESECURITYMODE = 1
                ) as SECURESITE

                on
                    SECURESITE.SITEID = REVENUESITEMAP.SITEID
                    or
                    (
                        SECURESITE.SITEID is null
                        and
                        REVENUESITEMAP.SITEID is null
                    )
            )