V_CMSSECURABLEOBJECTS

CREATE view [dbo].[V_CMSSECURABLEOBJECTS]
with schemabinding
as

            select
                GUID as OBJECTGUID,
                1 as OBJECTTYPEID,
                CLIENTSITESID,
                ID as RECORDID,
                OwnerID as OwnerID
            from dbo.SiteContent
            where (DELETED = 0 or INTRASHBIN = 1) AND GUID IS NOT NULL

            UNION ALL
            select
                GUID as OBJECTGUID,
                2 as OBJECTTYPEID,
                CLIENTSITESID,
                ID as RECORDID,
                OwnerID as OwnerID
            from dbo.SitePages
            where TemplatePageID != 0 AND (DELETED = 0 or INTRASHBIN = 1) AND GUID IS NOT NULL        

            UNION ALL
            select
                GUID as OBJECTGUID,
                3 as OBJECTTYPEID,
                CLIENTSITESID,
                ID as RECORDID,
                OwnerID as OwnerID
            from dbo.SitePages
            where TemplatePageID = 0 AND (DELETED = 0 or INTRASHBIN = 1) AND GUID IS NOT NULL

            UNION ALL
            select
                GUID as OBJECTGUID,
                4 as OBJECTTYPEID,
                CLIENTSITESID,
                ID as RECORDID,
                OwnerID as OwnerID
            from dbo.SiteImages
            where (DELETED = 0 or INTRASHBIN = 1) AND GUID IS NOT NULL

            UNION ALL
            select
                GUID as OBJECTGUID,
                8 as OBJECTTYPEID,
                ID as CLIENTSITESID,
                ID as RECORDID,
                OwnerID as OwnerID
            from dbo.ClientSites

            UNION ALL
            select
                GUID as OBJECTGUID,
                12 as OBJECTTYPEID,
                CLIENTSITESID,
                ID as RECORDID,
                OwnerID as OwnerID
            from dbo.EmailList
            where DELETED = 0 AND GUID IS NOT NULL

            UNION ALL
            select
                GUID as OBJECTGUID,
                14 as OBJECTTYPEID,
                CLIENTSITESID,
                ID as RECORDID,
                OwnerID as OwnerID
            from dbo.EmailTemplate
            where DELETED = 0 AND GUID IS NOT NULL

            UNION ALL
            select
                GUID as OBJECTGUID,
                15 as OBJECTTYPEID,
                CLIENTSITESID,
                ID as RECORDID,
                OwnerID as OwnerID
            from dbo.Email
            where DELETED = 0 AND GUID IS NOT NULL
            --WI 108343 Excluding several  Acknowledgement-like emails since they can't be secured or accessed as an owner
            --And excluding newsletters since we will handle them specially below
            and Type not in (1,11,14,12,18,19,2) 

            ---For newsletters issues, lets respect the site of the newsletter itself
            UNION ALL
            select
                E.GUID as OBJECTGUID,
                15 as OBJECTTYPEID,
                ET.CLIENTSITESID,
                E.ID as RECORDID,
                E.OwnerID as OwnerID
            from dbo.Email E
            inner join dbo.EmailTemplate ET on ET.ID = E.EmailTemplateID
            where E.DELETED = 0 AND E.GUID IS NOT NULL
            and E.Type = 2

            UNION ALL
            select
                GUID as OBJECTGUID,
                17 as OBJECTTYPEID,
                CLIENTSITESID,
                ID as RECORDID,
                OwnerID as OwnerID
            from dbo.SiteLayouts
            where DELETED = 0 AND GUID IS NOT NULL

            UNION ALL
            select
                GUID as OBJECTGUID,
                18 as OBJECTTYPEID,
                null as CLIENTSITESID,
                ID as RECORDID,
                null as OwnerID
            from dbo.ClientClassYears
            where GUID IS NOT NULL

            UNION ALL
            select
                O.GUID as OBJECTGUID,
                20 as OBJECTTYPEID,
                sc.CLIENTSITESID,
                O.ID as RECORDID,
                sc.OwnerID as OwnerID
            from dbo.DocUploadPart O
            INNER JOIN dbo.SiteContent sc on O.SiteContentID = sc.ID

            UNION ALL
            select
                O.GUID as OBJECTGUID,
                21 as OBJECTTYPEID,
                sc.CLIENTSITESID,
                O.ID as RECORDID,
                sc.OwnerID as OwnerID
            from dbo.DiscussionGroups O
            INNER JOIN dbo.SiteContent sc on O.SiteContentID = sc.ID
            where sc.DELETED = 0 AND O.GUID IS NOT NULL

            UNION ALL
            select
                O.GUID as OBJECTGUID,
                22 as OBJECTTYPEID,
                sc.CLIENTSITESID,
                O.ID as RECORDID,
                sc.OwnerID as OwnerID
            from dbo.JobBoards O
            INNER JOIN dbo.SiteContent sc on O.SiteContentID = sc.ID
            where sc.DELETED = 0 AND O.GUID IS NOT NULL

            UNION ALL
            select
                O.GUID as OBJECTGUID,
                25 as OBJECTTYPEID,
                sc.CLIENTSITESID,
                O.ID as RECORDID,
                sc.OwnerID as OwnerID
            from dbo.GivingHistory O
            INNER JOIN dbo.SiteContent sc on O.SiteContentID = sc.ID
            where sc.DELETED = 0 AND O.GUID IS NOT NULL

            UNION ALL
            select
                O.GUID as OBJECTGUID,
                26 as OBJECTTYPEID,
                sc.CLIENTSITESID,
                O.ID as RECORDID,
                sc.OwnerID as OwnerID
            from dbo.NewsReaders O
            INNER JOIN dbo.SiteContent sc on O.SiteContentID = sc.ID
            where sc.DELETED = 0 AND O.GUID IS NOT NULL

            UNION ALL
            select
                O.GUID as OBJECTGUID,
                27 as OBJECTTYPEID,
                sc.CLIENTSITESID,
                O.ID as RECORDID,
                sc.OwnerID as OwnerID
            from dbo.NewsChannels O
            INNER JOIN dbo.SiteContent sc on O.SiteContentID = sc.ID
            where sc.DELETED = 0 AND O.GUID IS NOT NULL

            UNION ALL
            select
                GUID as OBJECTGUID,
                30 as OBJECTTYPEID,
                CLIENTSITESID,
                ID as RECORDID,
                OwnerID as OwnerID
            from dbo.SiteStylesheets
            where DELETED = 0 AND GUID IS NOT NULL

            UNION ALL
            select
                O.CalendarCategoryPicksID as OBJECTGUID,
                31 as OBJECTTYPEID,
                sc.CLIENTSITESID,
                O.EventCalendarID as RECORDID,
                sc.OwnerID as OwnerID
            from dbo.CalendarEvtCategoryPicks O
            inner join dbo.EventCalendar ec on O.EventCalendarID = ec.ID
            inner join dbo.SiteContent sc on ec.SiteContentID = sc.ID
            where sc.DELETED = 0 AND O.CalendarCategoryPicksID IS NOT NULL

            UNION ALL
            select
                GUID as OBJECTGUID,
                33 as OBJECTTYPEID,
                CLIENTSITESID,
                ID as RECORDID,
                OwnerID as OwnerID
            from dbo.EmailProject
            where DELETED = 0 AND GUID IS NOT NULL

            UNION ALL
            select
                O.Guid as OBJECTGUID,
                38 as OBJECTTYPEID,
                cfs.ClientSitesID as CLIENTSITESID,
                O.ID as RECORDID,
                O.OwnerID as OwnerID
            from dbo.CustomFormElement O
            inner join dbo.CustomFormSettings cfs on O.Guid = cfs.FormGUID 
            where TypeID = 1 AND O.Guid IS NOT NULL

            UNION ALL
            select
                O.Guid as OBJECTGUID,
                41 as OBJECTTYPEID,
                O.ClientSitesID as CLIENTSITESID,
                O.ID as RECORDID,
                O.OwnerID as OwnerID
            from dbo.DOCUPLOADFILES O
            where O.Guid IS NOT NULL