USP_DATALIST_APPUSERTASKS

CREATE procedure dbo.USP_DATALIST_APPUSERTASKS (@APPUSERID uniqueidentifier)
as
    -- since a user can be a member of multiple roles each having been assigned a task, we need to de-dupe the assigned tasks

    declare @tbl Table (TASKID uniqueidentifier)
    insert into @tbl (TASKID) 
        select distinct TASKCATALOG.ID
        from dbo.TASKCATALOG 
        inner join dbo.SYSTEMROLETASK on TASKCATALOG.ID = SYSTEMROLETASK.TASKID
        inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SYSTEMROLETASK.SYSTEMROLEID
        where dbo.SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID

    select  TASKCATALOG.ID,
        TASKCATALOG.[NAME],
        TASKCATALOG.DESCRIPTION,
        FA.NAME as 'Functional Area',
        TASKCATALOG.TASKSPECXML.value(
            'declare namespace bbtask="bb_appfx_task";
            /bbtask:TaskSpec[1]/@ImageKey
            '
            ,
            'nvarchar(500)'
        ) AS TASKIMAGEKEY,
       case when 
            (select count(*) 
             from dbo.TASKCATALOG as TC
             inner join dbo.SYSTEMROLETASK on TC.ID = SYSTEMROLETASK.TASKID
             inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SYSTEMROLETASK.SYSTEMROLEID
             where (TC.ID = TASKCATALOG.ID) and 
                   (dbo.SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID) and 
                   (dbo.SYSTEMROLETASK.DISPLAYONHOMEPAGE = 1)) = 0
        then convert(bit, 0)
        else convert(bit, 1)
        end as 'DISPLAYONHOMEPAGE'
    from dbo.TASKCATALOG inner join dbo.FUNCTIONALAREACATALOG as FA on TASKCATALOG.FUNCTIONALAREAID = FA.ID
    where exists (select TASKID from @tbl where TASKID = TASKCATALOG.ID)
    order by FA.SEQUENCE, FA.[NAME], TASKCATALOG.SEQUENCE, TASKCATALOG.[NAME]