USP_DATAFORMTEMPLATE_VIEW_ONLINEINFOSUMMARY

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@COMMUNITYMEMBERUSERNAME	nvarchar(50)	INOUT
@COMMUNITYMEMBERUSERADDEDDATE	datetime	INOUT
@COMMUNITYMEMBERLASTLOGINDATE	datetime	INOUT
@EMAILINFORMATIONLOADED	bit	INOUT
@OPTEDIN	bit	INOUT
@FORWARDINGENABLED	bit	INOUT
@INCOMINGEMAILADDRESS	nvarchar(510)	INOUT
@FORWARDINGEMAILADDRESS	nvarchar(510)	INOUT
@ACCOUNTEMAILADDRESS	nvarchar(200)	INOUT
@CURRENTCONSTITUENTID	uniqueidentifier	INOUT
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ONLINEINFOSUMMARY
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @COMMUNITYMEMBERUSERNAME nvarchar(50) = null output,
                        @COMMUNITYMEMBERUSERADDEDDATE datetime = null output,
                        @COMMUNITYMEMBERLASTLOGINDATE datetime = null output,
                        @EMAILINFORMATIONLOADED bit = null output,
                        @OPTEDIN bit = null output,
                        @FORWARDINGENABLED bit = null output,
                        @INCOMINGEMAILADDRESS nvarchar(510) = null output,
                        @FORWARDINGEMAILADDRESS nvarchar(510) = null output,
                        @ACCOUNTEMAILADDRESS nvarchar(200) = null output,
                        @CURRENTCONSTITUENTID uniqueidentifier = null output
                    )
                    as

                        set nocount on;

                        set @DATALOADED = 0;
                        set @CURRENTCONSTITUENTID = @ID;

                        --Community Member

                        select top (1)
                            @DATALOADED = 1,
                            @COMMUNITYMEMBERUSERNAME = [USERNAME],
                            @COMMUNITYMEMBERUSERADDEDDATE = [USERADDEDDATE],
                            @COMMUNITYMEMBERLASTLOGINDATE = [LASTLOGINDATE]
                        from
                            dbo.NETCOMMUNITYCLIENTUSER
                        where
                            [CONSTITUENTID] = @ID
                            and [ACTIVE] = 1
                            and [DELETED] = 0
                        order by
                            [USERUPDATEDATE] desc;

                                            -- email forwarding info

                        set @EMAILINFORMATIONLOADED = 0;

                        select top (1)
                            @EMAILINFORMATIONLOADED =
                                case
                                    when [NETCOMMUNITYEMAILINFO].[ID] is null then 0
                                    else 1
                                end,
                            @OPTEDIN = [NETCOMMUNITYEMAILINFO].[OPTEDIN],
                            @FORWARDINGENABLED = [NETCOMMUNITYEMAILINFO].[FORWARDINGENABLED],
                            @INCOMINGEMAILADDRESS = [NETCOMMUNITYEMAILINFO].[INCOMINGEMAILADDRESS],
                            @FORWARDINGEMAILADDRESS = [NETCOMMUNITYEMAILINFO].[FORWARDINGEMAILADDRESS],
                            @ACCOUNTEMAILADDRESS = [CLIENTUSERS].[EMAIL]
                        from
                            dbo.NETCOMMUNITYCLIENTUSER
                            left join dbo.NETCOMMUNITYEMAILINFO on [NETCOMMUNITYCLIENTUSER].[ID] = [NETCOMMUNITYEMAILINFO].[NETCOMMUNITYCLIENTUSERID]
                            left join dbo.[CLIENTUSERS] on [NETCOMMUNITYCLIENTUSER].[BBNCUSERID] = [CLIENTUSERS].[ID] 
                        where
                            [NETCOMMUNITYCLIENTUSER].[CONSTITUENTID] = @ID
                        order by
                            [NETCOMMUNITYEMAILINFO].[UPDATEDATE] desc;

                        return 0;