UFN_QUERY_REPORTTASKREFERENCES

CREATE function dbo.UFN_QUERY_REPORTTASKREFERENCES
(
    @REPORTID uniqueidentifier
)
returns @TABLE table
(
    REPORTID uniqueidentifier,
    ID uniqueidentifier,
    NAME nvarchar(max),
    DESCRIPTION nvarchar(max),
    TASKGROUP nvarchar(max),
    SEQUENCE int,
    IMAGEKEY nvarchar(max)
)
with execute as caller
as
begin
    with xmlnamespaces ('bb_appfx_task' as tns, 'bb_appfx_commontypes' as common)
    insert into @TABLE
    select 
        @REPORTID,
        T.ID,
        T.NAME,
        T.DESCRIPTION,
        T.TASKGROUP,
        T.SEQUENCE,
        coalesce(T.TASKSPECXML.value('(tns:TaskSpec/@ImageKey)[1]', 'nvarchar(256)'), 'res:tasks') as TASKIMAGEKEY
    from dbo.TASKCATALOG as T
        inner join dbo.UFN_REPORT_TASKREFERENCES(@REPORTID) as TT on T.ID = TT.TASKID
        inner join dbo.FUNCTIONALAREACATALOG as F on T.FUNCTIONALAREAID = F.ID
    order by F.NAME, T.SEQUENCE, T.TASKGROUP, T.NAME;

    return;

end;