USP_DATALIST_REPORTSBYAPPUSER

CREATE procedure dbo.USP_DATALIST_REPORTSBYAPPUSER
(
    @CURRENTAPPUSERID uniqueidentifier,
    @ALLREPORTS bit = 0
)
as
    set nocount on;

    with XMLNAMESPACES('bb_appfx_report' as rp, 'bb_appfx_commontypes' as ct, 'bb_appfx_task' as t)
    select RC.ID, RC.NAME, RC.DESCRIPTION,
    RC.REPORTSPECXML.value('(/rp:ReportSpec/rp:Folder/node())[1]', 'nvarchar(max)') as FOLDER,
    (CASE WHEN RC.REPORTSPECXML.value('(/rp:ReportSpec/ct:MetaTags/AdHocQuerySaveReportRequest)[1]', 'nvarchar(max)') IS NULL THEN 0
            ELSE 1
            END) as ISADHOCQUERYREPORT,
        cast((case when exists(select top(1) ID 
                from dbo.TASKCATALOG 
                where TASKCATALOG.TASKSPECXML.value('(/t:TaskSpec/ct:ShowReport/@ReportID)[1]', 'uniqueidentifier') = RC.ID)
                then 1
                else 0
                end) as bit) as HASTASK,
    RC.UINAME,
    case 
        when coalesce(RC.REPORTSPECXML.value('(/rp:ReportSpec/@HideFromReportExplorer)[1]', 'bit'), 0) = 0 
            then 
                case
                    when @ALLREPORTS = 0 then 1
                    when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_REPORT_IN_SYSTEMROLE(@CURRENTAPPUSERID, RC.ID) = 1 then 1 
                    else 0 
                end
        else 0 
    end as ISVISIBLEINREPORTEXPLORER
    from dbo.V_INSTALLED_REPORTCATALOG RC
    where
        (
            @ALLREPORTS = 1
            or 
            (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_REPORT_IN_SYSTEMROLE(@CURRENTAPPUSERID, RC.ID) = 1)
        )
        and RC.REPORTSPECXML.value('(/rp:ReportSpec/rp:LinkedReport)[1]', 'nvarchar(max)') is null
    order by RC.UINAME