USP_DATALIST_SECURABLECODETABLE

Displays code tables that are securable to a system role.
Parameters

Parameter	Parameter Type	Mode	Description
@SYSTEMROLEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_SECURABLECODETABLE
                (
                    @SYSTEMROLEID uniqueidentifier
                )
                as
                    set nocount on;

                    select
                        cast(@SYSTEMROLEID as nchar(36)) + '|' + cast(CODETABLECATALOG.ID as nchar(36)),
                        CODETABLECATEGORY.DESCRIPTION as CATEGORY,
                        CODETABLECATALOG.UINAME as CODETABLENAME,
                        (
                            select count(SYSTEMROLEPERM_CODETABLEENTRY.ID) 
                            from dbo.SYSTEMROLEPERM_CODETABLEENTRY 
                            where SYSTEMROLEPERM_CODETABLEENTRY.CODETABLECATALOGID = CODETABLECATALOG.ID 
                            and SYSTEMROLEPERM_CODETABLEENTRY.SYSTEMROLEID = @SYSTEMROLEID
                            and SYSTEMROLEPERM_CODETABLEENTRY.ISDENIED = 1
                        ) as NUMDENIED
                    from
                        dbo.CODETABLECATALOG
                    inner join
                        dbo.CODETABLECATEGORY on CODETABLECATALOG.CATEGORYID = CODETABLECATEGORY.ID
                    where
                        CODETABLECATALOG.ISSECURABLE = 1
                        and 1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
                            (
                                CODETABLECATALOG.CODETABLESPECXML.query
                                    (
                                        'declare namespace common="bb_appfx_commontypes";
                                        /*/common:InstalledProductList'
                                    )
                            )
                    order by
                        CODETABLECATALOG.UINAME;