USP_DATAFORMTEMPLATE_VIEW_PRINTORSEND

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@NAME	nvarchar(368)	INOUT
@SUBJECT	nvarchar(510)	INOUT
@CONTENTHTML	nvarchar(max)	INOUT
@FROMDISPLAYNAME	nvarchar(510)	INOUT
@FROMADDRESS	nvarchar(510)	INOUT
@REPLYADDRESS	nvarchar(510)	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@EVENTID	uniqueidentifier	INOUT
@EMAILTEMPLATEID	int	INOUT
@REVENUEID	uniqueidentifier	INOUT
@BBNCURL	nvarchar(1024)	INOUT
@REGISTRANTID	uniqueidentifier	INOUT
@SEQUENCEID	int	INOUT
@EVENTNAME	nvarchar(200)	INOUT
@GROUPID	uniqueidentifier	INOUT
@GROUPNAME	nvarchar(200)	INOUT
@TRANSACTIONTYPECODE	tinyint	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PRINTORSEND
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(368) = null output,
    @SUBJECT nvarchar(510) = null output,
    @CONTENTHTML nvarchar(max) = null output,
    @FROMDISPLAYNAME nvarchar(510) = null output,
    @FROMADDRESS nvarchar(510) = null output,
    @REPLYADDRESS nvarchar(510) = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @EVENTID uniqueidentifier = null output,
    @EMAILTEMPLATEID int = null output,
    @REVENUEID uniqueidentifier = null output,
    @BBNCURL nvarchar(1024) = null output,
    @REGISTRANTID uniqueidentifier = null output,
    @SEQUENCEID int = null output,
    @EVENTNAME nvarchar(200) = null output,
    @GROUPID uniqueidentifier = null output,
    @GROUPNAME nvarchar(200) = null output,
    @TRANSACTIONTYPECODE tinyint = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.
    select @DATALOADED = 1,
    @ID = r.ID,
    @NAME = et.Name,
    @SUBJECT = et.Subject,
    @CONTENTHTML = et.ContentHtml,
    @FROMDISPLAYNAME = et.FromDisplayName,
    @FROMADDRESS = et.FromAddress,
    @REPLYADDRESS = et.ReplyAddress,    
    @CONSTITUENTID = r.CONSTITUENTID,
    @EVENTID = e.ID,
    @EMAILTEMPLATEID = et.[ID],
    @REVENUEID = r.ID,
    @BBNCURL = dbo.UFN_BBNC_URL(),
    @REGISTRANTID = isnull( re.[ID], r2.[ID]),
    @SEQUENCEID = c.[SEQUENCEID],
    @EVENTNAME = e.[NAME],
    @GROUPID = te.[TEAMFUNDRAISINGTEAMID],
    @GROUPNAME = case WHEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID) IS NOT NULL 
    THEN dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(RR.CONSTITUENTID)      
    ELSE (SELECT CS.NAME FROM dbo.CONSTITUENT CS WHERE CS.ID=RR.CONSTITUENTID) END,
    @TRANSACTIONTYPECODE = r.[TRANSACTIONTYPECODE]
    from dbo.REVENUE r (nolock)
  inner join dbo.REVENUESPLIT rs (nolock) 
    on r.ID = rs.REVENUEID    
  left join REVENUERECOGNITION rr (nolock) 
    on rr.REVENUESPLITID = rs.ID  
  inner join EVENT e (nolock)
    on e.APPEALID = r.APPEALID  
  left join  dbo.EVENTEMAILTEMPLATE eet (nolock)
    on eet.EVENTID = e.ID 
  join EMAILTEMPLATE et (nolock)
    on eet.EMAILTEMPLATEID = et.ID
left join REGISTRANT re (nolock)
    on r.[CONSTITUENTID] = re.[CONSTITUENTID] --and re.[EVENTID] = e.[ID]
  left join CONSTITUENT c (nolock)
    on re.[CONSTITUENTID] = c.[ID] 
  left join TEAMEXTENSION te
    on te.TEAMCONSTITUENTID = rr.[CONSTITUENTID] and te.[EVENTID] = e.[ID]
    left join REGISTRANT r2
    on     r2.CONSTITUENTID = rr.[CONSTITUENTID] and r2.[EVENTID] = e.[ID]
  where EET.CONFIRMATIONTYPECODE = 3  
  and r.ID = @ID

    return 0;