USP_DATAFORMTEMPLATE_EDITLOAD_GROUPMEMBERREMOVE

The load procedure used by the edit dataform template "Remove Group Member Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@GROUPNAME	nvarchar(700)	INOUT	Group
@MEMBERNAME	nvarchar(700)	INOUT	Member
@MAINTAINHISTORY	bit	INOUT	Maintain member history with group
@DATETO	date	INOUT	The constituent was a member through
@COMMENTS	nvarchar(300)	INOUT	Comments
@ISPLANPARTICIPANT	bit	INOUT	Is plan participant
@ISINTERACTIONPARTICIPANT	bit	INOUT	Is step participant
@HASOPPORTUNITYDESIGNATION	bit	INOUT	Has opportunity designation
@ISHOUSEHOLD	bit	INOUT	Is Household
@HASRECOGNITIONDEFAULTS	bit	INOUT	Has recognition default with household
@CLEARRECOGNITIONDEFAULTS	bit	INOUT	Stop member from receiving recognition
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_GROUPMEMBERREMOVE (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @GROUPNAME nvarchar(700) = null output,
                    @MEMBERNAME nvarchar(700) = null output,
                    @MAINTAINHISTORY bit = null output,
                    @DATETO date = null output,
                    @COMMENTS nvarchar(300) = null output,
                    @ISPLANPARTICIPANT bit = null output,
                    @ISINTERACTIONPARTICIPANT bit = null output,
                    @HASOPPORTUNITYDESIGNATION bit = null output,
                    @ISHOUSEHOLD bit = null output,
                    @HASRECOGNITIONDEFAULTS bit = null output,
                    @CLEARRECOGNITIONDEFAULTS bit = null output,
                    @TSLONG bigint = 0 output
                )
                as begin
                    set nocount on;

                    set @DATALOADED = 0;
                    set @TSLONG = 0;
                    set @MAINTAINHISTORY = 1;
                    set @DATETO = getdate();
                    set @CLEARRECOGNITIONDEFAULTS = 0;

                    declare @GROUPID uniqueidentifier, @MEMBERID uniqueidentifier
                    select
                        @DATALOADED = 1,
                        @GROUPNAME = GROUP_NF.NAME,
                        @MEMBERNAME = MEMBER_NF.NAME,
                        @ISHOUSEHOLD = dbo.UFN_CONSTITUENT_ISHOUSEHOLD(GM.GROUPID),
                        @GROUPID = GM.GROUPID,
                        @MEMBERID = GM.MEMBERID
                    from 
                        dbo.GROUPMEMBER as GM
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GM.MEMBERID) MEMBER_NF
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GM.GROUPID) GROUP_NF
                    where 
                        GM.ID = @ID;

                    select @ISPLANPARTICIPANT = case count(PP.ID) when 0 then 0 else 1 end 
                    from dbo.GROUPMEMBER as GM
                        inner join dbo.PLANPARTICIPANT as PP on GM.MEMBERID = PP.CONSTITUENTID
                        inner join dbo.PROSPECTPLAN as PP2 on PP.PROSPECTPLANID = PP2.ID
                    where 
                        GM.ID = @ID
                        and PP2.PROSPECTID = GM.GROUPID;

                    select @ISINTERACTIONPARTICIPANT = case count (IP.ID) when 0 then 0 else 1 end
                    from dbo.GROUPMEMBER as GM
                        inner join dbo.INTERACTIONPARTICIPANT as IP on GM.MEMBERID = IP.CONSTITUENTID
                        inner join dbo.INTERACTION as I on IP.INTERACTIONID = I.ID
                    where
                        GM.ID = @ID
                        and I.CONSTITUENTID = GM.GROUPID;

                    select @HASOPPORTUNITYDESIGNATION = case count(OD.ID) when 0 then 0 else 1 end 
                    from dbo.GROUPMEMBER as GM
                        join dbo.OPPORTUNITYDESIGNATION as OD on GM.MEMBERID = OD.CONSTITUENTID
                    where 
                        GM.ID = @ID;

                    select @HASRECOGNITIONDEFAULTS = case count(RD.ID) when 0 then 0 else 1 end
                    from dbo.REVENUERECOGNITIONDEFAULT RD
                    where
                        RD.SOURCECONSTITUENTID = @GROUPID and
                        RD.RECIPIENTCONSTITUENTID = @MEMBERID

                    return 0;
                end