USP_DATAFORMTEMPLATE_VIEW_REGISTRANTSIMPLE

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@EVENTID	uniqueidentifier	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@TEAMFUNDRAISERID	uniqueidentifier	INOUT
@GROUPCONSTITUENTID	uniqueidentifier	INOUT
@PARENTGROUPCONSTITUENTID	uniqueidentifier	INOUT
@GROUPTYPECODE	tinyint	INOUT
@LEADERROLECODE	tinyint	INOUT
@MEMBERS	xml	INOUT
@TEAMFUNDRAISINGTEAMID	uniqueidentifier	INOUT
@PARENTGROUPID	uniqueidentifier	INOUT
@REGISTRATIONOPTIONID	uniqueidentifier	INOUT
@FEE	money	INOUT
@GROUPNAME	nvarchar(200)	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REGISTRANTSIMPLE
(
    @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
    @EVENTID uniqueidentifier = null output,
    @CONSTITUENTID uniqueidentifier = null output,
  @TEAMFUNDRAISERID uniqueidentifier = null output,
    @GROUPCONSTITUENTID uniqueidentifier = null output,
  @PARENTGROUPCONSTITUENTID uniqueidentifier = null output,
  @GROUPTYPECODE tinyint = null output,
  @LEADERROLECODE tinyint = null output,
  @MEMBERS xml = null output,
  @TEAMFUNDRAISINGTEAMID uniqueidentifier = null output,
  @PARENTGROUPID uniqueidentifier = null output,
    @REGISTRATIONOPTIONID uniqueidentifier = null output,
    @FEE money = null output,
  @GROUPNAME nvarchar(200) = null output
)
as
    set nocount on;

    set @DATALOADED = 0

      select @DATALOADED = 1, 
        @EVENTID = R.EVENTID,
            @CONSTITUENTID = R.CONSTITUENTID,
            --@REGISTRATIONOPTIONID = EP.ID,
            @FEE = RR.AMOUNT       
    from dbo.REGISTRANT R 
    join dbo.REGISTRANTREGISTRATION RR ON RR.REGISTRANTID = R.ID
    join dbo.EVENTPRICE EP ON RR.EVENTPRICEID = EP.ID   
      left join dbo.EVENTREGISTRANTPAYMENT ERP on ERP.REGISTRANTID = R.ID 
    where R.ID = @ID

    select @TEAMFUNDRAISINGTEAMID=TFTM.TEAMFUNDRAISINGTEAMID 
    from dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock)
    join dbo.TEAMFUNDRAISER TF (nolock) on TFTM.TEAMFUNDRAISERID = TF.ID and TF.CONSTITUENTID = @CONSTITUENTID
    join dbo.TEAMEXTENSION TX (nolock) on TFTM.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
    where TX.EVENTID = @EVENTID    

    select @GROUPCONSTITUENTID = TE.TEAMCONSTITUENTID,
           @PARENTGROUPCONSTITUENTID = pTX.TEAMCONSTITUENTID,
           @TEAMFUNDRAISINGTEAMID = T.ID,
           @PARENTGROUPID = T.PARENTTEAMID,
           @GROUPTYPECODE = TE.TYPECODE,
           @GROUPNAME = T.NAME,
           @LEADERROLECODE = case when TE.TYPECODE = 2 then 0 else TE.TYPECODE end -- set using group code to derive leader role code 
    from dbo.TEAMFUNDRAISINGTEAM T (nolock)
    join dbo.TEAMEXTENSION TE (nolock) ON T.ID = TE.TEAMFUNDRAISINGTEAMID       
      left join dbo.TEAMEXTENSION pTX (nolock) on pTX.TEAMFUNDRAISINGTEAMID = T.PARENTTEAMID
    where T.ID = @TEAMFUNDRAISINGTEAMID

    select @MEMBERS = (
        select C.NAME, C.ID as CONSTITUENTID, TF.ID as TEAMFUNDRAISERID 
        from dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock)
        join dbo.TEAMFUNDRAISER TF (nolock) on TFTM.TEAMFUNDRAISERID = TF.ID 
        join dbo.CONSTITUENT C (nolock) on TF.CONSTITUENTID = C.ID 
        where TFTM.TEAMFUNDRAISINGTEAMID =  @TEAMFUNDRAISINGTEAMID and C.ID <> @CONSTITUENTID
        for xml raw('ITEM'), type, elements, root('MEMBERS'), BINARY BASE64
    )