USP_DATAFORMTEMPLATE_VIEW_PROSPECTRESEARCHREQUEST

The load procedure used by the view dataform template "Prospect Research Request View"

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.
@STATUS	nvarchar(100)	INOUT	Status:
@PRIORITY	nvarchar(150)	INOUT	Priority:
@REQUESTDATE	date	INOUT	Request date:
@REASON	nvarchar(150)	INOUT	Request reason:
@REQUESTEDBYID	uniqueidentifier	INOUT	Requested by:
@SUBMITTEDBYID	uniqueidentifier	INOUT	Submitted by:
@NOTES	nvarchar(max)	INOUT	Request notes:
@STATUSCHANGENOTES	nvarchar(max)	INOUT	Status comments:
@RECORDTYPE	nvarchar(14)	INOUT	Record type:
@NUMBEROFCONSTITUENTS	smallint	INOUT	No. prospects:
@RESEARCHGROUPID	uniqueidentifier	INOUT	Research group ID
@EVENTID	uniqueidentifier	INOUT	Event ID
@RESEARCHGROUPNAME	nvarchar(150)	INOUT	Research group name
@EVENTNAME	nvarchar(150)	INOUT	Event name
@RECORDTYPECODE	tinyint	INOUT	Record type code
@SITES	nvarchar(max)	INOUT	Sites

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROSPECTRESEARCHREQUEST
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @STATUS nvarchar(100) = null output,
    @PRIORITY nvarchar(150) = null output,
    @REQUESTDATE date = null output,
    @REASON nvarchar(150) = null output,
    @REQUESTEDBYID uniqueidentifier = null output,
    @SUBMITTEDBYID uniqueidentifier = null output,
    @NOTES nvarchar(max) = null output,
    @STATUSCHANGENOTES nvarchar(max) = null output,
    @RECORDTYPE nvarchar(14) = null output,
    @NUMBEROFCONSTITUENTS smallint = null output,
    @RESEARCHGROUPID uniqueidentifier = null output,
    @EVENTID uniqueidentifier = null output,
    @RESEARCHGROUPNAME nvarchar(150) = null output,
    @EVENTNAME nvarchar(150) = null output,
    @RECORDTYPECODE tinyint = null output,
    @SITES nvarchar(max) = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    declare @STATUSCHANGEREASON nvarchar(100)

    select 
        @DATALOADED = 1,
        @STATUS = PRR.STATUS,
        @PRIORITY = PRRP.DESCRIPTION,
        @REQUESTDATE = PRR.DATEADDED,
        @REASON = PRRR.DESCRIPTION,
        @REQUESTEDBYID = PRR.REQUESTEDBYID,
        @SUBMITTEDBYID = PRR.SUBMITTEDBYID,
        @NOTES = case when len(PRR.NOTES) > 160 then (left(PRR.NOTES, 160) + '...') else PRR.NOTES end,
        @RECORDTYPE = PRR.RECORDTYPE,
        @NUMBEROFCONSTITUENTS = (select count(ID) from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT PRRC where PRRC.PROSPECTRESEARCHREQUESTID = PRR.ID and PRRC.STATUSCODE <> 6),
        @RESEARCHGROUPID = PRR.RESEARCHGROUPID,
        @EVENTID = PRR.EVENTID,
        @RESEARCHGROUPNAME = RG.NAME,
        @EVENTNAME = E.NAME,
        @STATUSCHANGEREASON = PRRSR.DESCRIPTION,
        @STATUSCHANGENOTES = case when len(PRR.STATUSCHANGENOTES) > 160 then (left(PRR.STATUSCHANGENOTES, 160) + '...') else PRR.STATUSCHANGENOTES end,
        @RECORDTYPECODE = PRR.RECORDTYPECODE,
        @SITES =    (
                        select dbo.UDA_BUILDLIST(SITE.NAME) 
                        from dbo.PROSPECTRESEARCHREQUESTSITE
                        inner join dbo.SITE on SITE.ID = PROSPECTRESEARCHREQUESTSITE.SITEID
                        where PROSPECTRESEARCHREQUESTID = PRR.ID
                    )
    from 
        dbo.PROSPECTRESEARCHREQUEST PRR
    left join
        dbo.PROSPECTRESEARCHREQUESTPRIORITYCODE PRRP on PRRP.ID = PRR.PROSPECTRESEARCHREQUESTPRIORITYCODEID
    left join
        dbo.PROSPECTRESEARCHREQUESTREASONCODE PRRR on PRRR.ID = PRR.PROSPECTRESEARCHREQUESTREASONCODEID
    left join
        dbo.EVENT E on E.ID = PRR.EVENTID
    left join
        dbo.RESEARCHGROUP RG on RG.ID = PRR.RESEARCHGROUPID
    left join
        dbo.PROSPECTRESEARCHREQUESTSTATUSCHANGECODE PRRSR on PRRSR.ID = PRR.PROSPECTRESEARCHREQUESTSTATUSCHANGECODEID
    where 
        PRR.ID = @ID

    if (@STATUS = 'Canceled' or @STATUS = 'Rejected') and @STATUSCHANGEREASON is not null begin
        if len(@STATUSCHANGEREASON) > 36
            set @STATUS = @STATUS + ' (' + substring(@STATUSCHANGEREASON, 0, 33) + '...)'
        else
            set @STATUS = @STATUS + ' (' + @STATUSCHANGEREASON + ')'
    end

    return 0;