USP_BBNC_INTEGRATIONENDPOINTSLOAD

Retrieves data endpoints for the Blackbaud Internet Solutions integration service.
Parameters

Parameter	Parameter Type	Mode
@CLIENTUSERENDDATE	datetime	INOUT
@CLIENTUSERENDID	int	INOUT
@EMAILINFOENDDATE	datetime	INOUT
@EMAILJOBRECIPIENTENDDATE	datetime	INOUT
@EMAILJOBRECIPIENTENDID	int	INOUT
@TEAMFUNDRAISEREMAILENDDATE	datetime	INOUT
@NEWSLETTERSUBSCRIPTIONENDDATE	datetime	INOUT
@NEWSLETTERSUBSCRIPTIONENDID	int	INOUT
@WEBTRAFFICENDDATE	datetime	INOUT
Definition

Copy


            CREATE procedure dbo.USP_BBNC_INTEGRATIONENDPOINTSLOAD
            (
                @CLIENTUSERENDDATE datetime = null output,
                @CLIENTUSERENDID int = null output,
                @EMAILINFOENDDATE datetime = null output,
                @EMAILJOBRECIPIENTENDDATE datetime = null output,
                @EMAILJOBRECIPIENTENDID int = null output,
                @TEAMFUNDRAISEREMAILENDDATE datetime = null output,
                @NEWSLETTERSUBSCRIPTIONENDDATE datetime = null output,
                @NEWSLETTERSUBSCRIPTIONENDID int = null output,
                @WEBTRAFFICENDDATE datetime = null output
            )
            as
                set nocount on;

                begin try
                    select top (1)
                        @CLIENTUSERENDDATE = [NETCOMMUNITYCLIENTUSER].[USERUPDATEDATE],
                        @CLIENTUSERENDID = [NETCOMMUNITYCLIENTUSER].[BBNCUSERID]
                    from
                        dbo.NETCOMMUNITYCLIENTUSER
                    where
                        [NETCOMMUNITYCLIENTUSER].[USERUPDATEDATE] is not null
                    order by
                        [NETCOMMUNITYCLIENTUSER].[USERUPDATEDATE] desc,
                        [NETCOMMUNITYCLIENTUSER].[BBNCUSERID] desc;

                    select top (1)
                        @EMAILINFOENDDATE = [NETCOMMUNITYEMAILINFO].[UPDATEDATE]
                    from
                        dbo.NETCOMMUNITYEMAILINFO
                    where
                        [NETCOMMUNITYEMAILINFO].[UPDATEDATE] is not null
                    order by
                        [NETCOMMUNITYEMAILINFO].[UPDATEDATE] desc;

          select @EMAILJOBRECIPIENTENDDATE = UPDATEDATE from dbo.SERVERSTATUS where ID = 4 --ID 4 represent email job recipient update date


                    select top (1)
                        @EMAILJOBRECIPIENTENDDATE = coalesce(@EMAILJOBRECIPIENTENDDATE,[NETCOMMUNITYEMAILJOBRECIPIENT].[UPDATEDATE]),
                        @EMAILJOBRECIPIENTENDID = [NETCOMMUNITYEMAILJOBRECIPIENT].[BBNCMAPID]
                    from
                        dbo.NETCOMMUNITYEMAILJOBRECIPIENT
                    where
                        [NETCOMMUNITYEMAILJOBRECIPIENT].[UPDATEDATE] is not null
                    order by
                        [NETCOMMUNITYEMAILJOBRECIPIENT].[UPDATEDATE] desc,
                        [NETCOMMUNITYEMAILJOBRECIPIENT].[BBNCMAPID] desc;

                    select top (1)
                        @TEAMFUNDRAISEREMAILENDDATE = [NETCOMMUNITYTEAMFUNDRAISEREMAIL].[UPDATEDATE]
                    from
                        dbo.NETCOMMUNITYTEAMFUNDRAISEREMAIL
                    where
                        [NETCOMMUNITYTEAMFUNDRAISEREMAIL].[UPDATEDATE] is not null
                    order by
                        [NETCOMMUNITYTEAMFUNDRAISEREMAIL].[UPDATEDATE] desc;

                    select top (1)
                        @NEWSLETTERSUBSCRIPTIONENDDATE = coalesce([NETCOMMUNITYEMAILJOBRECIPIENT].[UPDATEDATE],[NETCOMMUNITYNEWSLETTERSUBSCRIPTION].[UPDATEDATE]),
                        @NEWSLETTERSUBSCRIPTIONENDID = [NETCOMMUNITYNEWSLETTERSUBSCRIPTION].[EMAILLISTMAPID]
                    from
                        dbo.NETCOMMUNITYNEWSLETTERSUBSCRIPTION
                        left join dbo.NETCOMMUNITYEMAILJOBRECIPIENT on [NETCOMMUNITYNEWSLETTERSUBSCRIPTION].[LATESTEMAILJOBRECIPIENTID] = [NETCOMMUNITYEMAILJOBRECIPIENT].[ID]
                    where
                        coalesce([NETCOMMUNITYEMAILJOBRECIPIENT].[UPDATEDATE],[NETCOMMUNITYNEWSLETTERSUBSCRIPTION].[UPDATEDATE]) is not null
                    order by
                        coalesce([NETCOMMUNITYEMAILJOBRECIPIENT].[UPDATEDATE],[NETCOMMUNITYNEWSLETTERSUBSCRIPTION].[UPDATEDATE]) desc,
                        [NETCOMMUNITYNEWSLETTERSUBSCRIPTION].[EMAILLISTMAPID] desc;

                    select top (1)
                        @WEBTRAFFICENDDATE = [NETCOMMUNITYWEBTRAFFIC].[REQUESTDATE]
                    from
                        dbo.NETCOMMUNITYWEBTRAFFIC
                    where
                        [NETCOMMUNITYWEBTRAFFIC].[REQUESTDATE] is not null
                    order by
                        [NETCOMMUNITYWEBTRAFFIC].[REQUESTDATE] desc;

                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;