UFN_APPUSER_GETAVAILABLESUMMARYTILESLOCALIZATION

create function dbo.UFN_APPUSER_GETAVAILABLESUMMARYTILESLOCALIZATION
(
    @APPUSERID uniqueidentifier,
    @SUMMARYTILECONTEXTID uniqueidentifier
)
returns table
as
return
(
    select
        AVAILABLESUMMARYTILE.DATAFORMINSTANCECATALOGID,
        AVAILABLESUMMARYTILE.TILENAME,
        DATAFORMTEMPLATECATALOG.TEMPLATESPECXML,

        --CATEGORYKEY should be the same for every row, so returning it here

        --is a bit clunky but we don't expect more than a handfull of rows

        SUMMARYTILECONTEXT.CATEGORYKEY
    from
        dbo.AVAILABLESUMMARYTILE
        inner join dbo.DATAFORMINSTANCECATALOG on AVAILABLESUMMARYTILE.DATAFORMINSTANCECATALOGID = DATAFORMINSTANCECATALOG.ID
        inner join dbo.DATAFORMTEMPLATECATALOG on DATAFORMINSTANCECATALOG.DATAFORMTEMPLATECATALOGID = DATAFORMTEMPLATECATALOG.ID
        inner join dbo.SUMMARYTILECONTEXT on AVAILABLESUMMARYTILE.SUMMARYTILECONTEXTID = SUMMARYTILECONTEXT.ID
    where
        AVAILABLESUMMARYTILE.SUMMARYTILECONTEXTID = @SUMMARYTILECONTEXTID
        and dbo.UFN_DATAFORMINSTANCE_PRODUCTISDATAFORM(DATAFORMINSTANCECATALOGID) = 1 --at least one of the product flags on the tile view form must be installed

        and (
            dbo.UFN_APPUSER_ISSYSADMIN(@APPUSERID) = 1 or
            dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@APPUSERID, DATAFORMINSTANCECATALOGID) = 1
        )
)