UFN_QUERY_FUNCTIONALAREAS

                create function dbo.[UFN_QUERY_FUNCTIONALAREAS](

                )

                    returns @result table(IMAGEKEY nvarchar(500), ID uniqueidentifier, NAME nvarchar(100), DESCRIPTION nvarchar(max), NUMBEROFTASKS int, SEQUENCE int, ISCUSTOMIZABLE bit)

                as
                    begin
                        declare @FUNCTIONALAREANUMTASKS table(FUNCTIONALAREAID uniqueidentifier, NUMBEROFTASKS int);

                        insert into @FUNCTIONALAREANUMTASKS
                        select FUNCTIONALAREAID, count(FUNCTIONALAREAID) as NUMBEROFTASKS from dbo.[TASKCATALOG] group by FUNCTIONALAREAID

                        insert into @result
                        select FA.FUNCTIONALAREASPECXML.value('declare namespace bbfa="bb_appfx_functionalarea";
                                                               /bbfa:FunctionalAreaSpec[1]/@ImageKey', 'nvarchar(500)') [IMAGEKEY],
                               FA.ID,
                               FA.[NAME],
                               FA.[DESCRIPTION],
                               isnull(FANT.[NUMBEROFTASKS], 0),
                               FA.[SEQUENCE],
                               coalesce(FA.FUNCTIONALAREASPECXML.value('declare namespace bbfa="bb_appfx_functionalarea";
                                                                        /bbfa:FunctionalAreaSpec[1]/@IsCustomizable', 'bit'), 1) [ISCUSTOMIZABLE]
                        from dbo.FUNCTIONALAREACATALOG as FA
                        left join @FUNCTIONALAREANUMTASKS as FANT on FA.ID = FANT.FUNCTIONALAREAID
                        where
                            1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
                            (
                                FA.FUNCTIONALAREASPECXML.query
                                    (
                                        'declare namespace common="bb_appfx_commontypes";
                                        /*/common:InstalledProductList'
                                    )
                            )

                        return;
                    end