USP_DATALIST_FUNCTIONALAREAS

CREATE procedure dbo.USP_DATALIST_FUNCTIONALAREAS
as
    set nocount on;

    select FA.FUNCTIONALAREASPECXML.value('declare namespace bbfa="bb_appfx_functionalarea";
                                           /bbfa:FunctionalAreaSpec[1]/@ImageKey', 'nvarchar(500)'),
           FA.ID,
           FA.[NAME],
           FA.DESCRIPTION,
           (select count(*) from dbo.TASKCATALOG where FUNCTIONALAREAID = FA.ID) as 'Number of Tasks',
           FA.SEQUENCE,
           coalesce(FA.FUNCTIONALAREASPECXML.value('declare namespace bbfa="bb_appfx_functionalarea";
                                                    /bbfa:FunctionalAreaSpec[1]/@IsCustomizable', 'bit'), 1)
    from dbo.FUNCTIONALAREACATALOG as FA
    where
        1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
        (
            FA.FUNCTIONALAREASPECXML.query
                (
                    'declare namespace common="bb_appfx_commontypes";
                    /*/common:InstalledProductList'
                )
        )
    order by FA.SEQUENCE, FA.[NAME]