USP_DATALIST_DOCUMENTLIBRARY

CREATE procedure dbo.USP_DATALIST_DOCUMENTLIBRARY(@CMSUSERID int)
as
    set nocount on;

declare @SITESWITHFULLPARTRIGHTS table (SITEID int)
insert into @SITESWITHFULLPARTRIGHTS
select CLIENTSITESID from [dbo].[UFN_CLIENTUSERS_TASKS](@CMSUSERID,'6AFA81B1-0B5F-454B-B810-A17D72EB70B6')

if EXISTS(SELECT 1 from @SITESWITHFULLPARTRIGHTS where SITEID is null) or EXISTS(select 1 from [dbo].[ClientUsers] where ID = @CMSUSERID and (IsSupervisor = 1 or InternalUser = 1))
begin
    select distinct SC.Title
    from dbo.DOCUPLOADFILES AS DF
    inner join dbo.DOCUPLOADPART DP on DP.ID = DF.CID
    inner join dbo.SITECONTENT SC on SC.ID = DP.SiteContentID        
    left outer join (select PAGEMODELITEM.OBJECTGUID from [dbo].PAGEMODEL inner join [dbo].PAGEMODELITEM on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID where PAGEMODEL.LOCKARTIFACTS=1)PM on PM.OBJECTGUID = sc.Guid
    where DF.APPROVED = 1 and PM.OBJECTGUID is null  and SC.ContentTypesID != 55
end
else
begin
    select distinct SC.Title
    from dbo.DOCUPLOADFILES AS DF
    inner join dbo.DOCUPLOADPART DP on DP.ID = DF.CID
    inner join dbo.SITECONTENT SC on SC.ID = DP.SiteContentID
    left outer join [dbo].[UFN_CLIENTUSERS_TASKS](@CMSUSERID,'6AFA81B1-0B5F-454B-B810-A17D72EB70B6') as [ALLRIGHTS] on [ALLRIGHTS].CLIENTSITESID = sc.ClientSitesID
    left outer join [dbo].[UFN_CLIENTUSERS_OBJECTTASKOBJECTS](@CMSUSERID,0,1) SECview on sc.Guid=SECview.OBJECTGUID
    left outer join (select PAGEMODELITEM.OBJECTGUID from [dbo].PAGEMODEL inner join [dbo].PAGEMODELITEM on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID where PAGEMODEL.LOCKARTIFACTS=1)PM on PM.OBJECTGUID = sc.Guid
    where DF.APPROVED = 1 and PM.OBJECTGUID is null and (ISNULL(SECview.VALUE, 0) + coalesce((select 1 where sc.OwnerID=@CMSUSERID),0) + ISNULL([ALLRIGHTS].CLIENTSITESID,0)) <> 0 and SC.ContentTypesID != 55
end