UFN_CMS_BBPAY_CONTENTNEEDINGUPDATE_DELIVERYCHANNEL

CREATE function dbo.UFN_CMS_BBPAY_CONTENTNEEDINGUPDATE_DELIVERYCHANNEL
(
    @FORCEUPDATE bit
)
returns @RETTABLE table
(
    SITECONTENTID   integer not null,
    SITEPAGEID      integer not null,
    DELIVERYCHANNELID uniqueidentifier
)
as 
begin
    declare
        -- older CMS artifacts saved with UTC datetime; need to offset those times.

        @UTC_OFFSET integer =       datediff(minute, getdate(), getutcdate()),
        -- wait until artifact changes are at least 5 minutes old before updating template

        @WAIT_TIME datetime =       dateadd(minute, -5, getdate()),
        @WAIT_TIME_UTC datetime =   dateadd(minute, -5, getutcdate());

    insert into @RETTABLE select distinct
        SiteContent.ID,
        SitePages.ID,
        CMSTEMPLATELAYOUT.CMSDELIVERYCHANNELID
    from 
        dbo.SiteContent 
            left join (select PAGEMODELITEM.OBJECTGUID 
                        from [dbo].PAGEMODEL 
                            inner join [dbo].PAGEMODELITEM on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
                        where PAGEMODEL.LOCKARTIFACTS = 1) PM on PM.OBJECTGUID = SiteContent.Guid

        join dbo.PageContent            on PageContent.SiteContentID = SiteContent.ID
        left join dbo.CRMBBPAYTEMPLATE  on CRMBBPAYTEMPLATE.SITEPAGEID = PageContent.SitePagesID
                                                and CRMBBPAYTEMPLATE.SITECONTENTID = SiteContent.ID
        join dbo.SitePages              on SitePages.ID = PageContent.SitePagesID
        join dbo.SitePages Templates    on Templates.ID = SitePages.TemplatePageID
        join dbo.CMSTEMPLATELAYOUT      on CMSTEMPLATELAYOUT.TEMPLATEID = Templates.ID 
            --and CMSTEMPLATELAYOUT.CMSDELIVERYCHANNELID = '23B56292-0F3C-43AC-AF13-7767AD9D4057' -- desktop only; known value

        join dbo.SiteLayouts            on SiteLayouts.ID = CMSTEMPLATELAYOUT.SITELAYOUTSID
        left join dbo.SiteStyleSheets        on SiteStyleSheets.ID = SiteLayouts.StyleSheetID
        left join dbo.THEME            on SitePages.ClientSitesID = THEME.CLIENTSITESID
    where 
        SiteContent.BBSPEnabled = 1  
        and dbo.fnSiteContentIsPublished(SiteContent.ID, getutcdate()) = 1
        and dbo.UFN_CMS_SITEISPUBLISHED(SitePages.ClientSitesID) = 1
        and PM.OBJECTGUID is null   -- no page model stuff

        and (@FORCEUPDATE = 1  or
            ((SiteContent.UpdateDate           between dateadd(minute, @UTC_OFFSET, isnull(CRMBBPAYTEMPLATE.DATECHANGED, '1900-01-01')) and @WAIT_TIME_UTC
            or  SitePages.UpdateDate           between dateadd(minute, @UTC_OFFSET, isnull(CRMBBPAYTEMPLATE.DATECHANGED, '1900-01-01')) and @WAIT_TIME_UTC
            or  Templates.UpdateDate           between dateadd(minute, @UTC_OFFSET, isnull(CRMBBPAYTEMPLATE.DATECHANGED, '1900-01-01')) and @WAIT_TIME_UTC
            or  CMSTEMPLATELAYOUT.DATECHANGED  between isnull(CRMBBPAYTEMPLATE.DATECHANGED, '1900-01-01') and @WAIT_TIME
            or  SiteLayouts.UpdateDate         between dateadd(minute, @UTC_OFFSET, isnull(CRMBBPAYTEMPLATE.DATECHANGED, '1900-01-01')) and @WAIT_TIME_UTC
            or  SiteStyleSheets.UpdateDate     between dateadd(minute, @UTC_OFFSET, isnull(CRMBBPAYTEMPLATE.DATECHANGED, '1900-01-01')) and @WAIT_TIME_UTC
            or  THEME.DATECHANGED           between isnull(CRMBBPAYTEMPLATE.DATECHANGED, '1900-01-01') and @WAIT_TIME)))

    return;
end