USP_DATAFORMTEMPLATE_VIEW_PROSPECTRESEARCHREQUESTCONSTITUENTSUMMARY

The load procedure used by the view dataform template "Prospect Research Request Constituent Summary 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.
@REQUESTNO	nvarchar(100)	INOUT	Request ID
@CONSTITUENT	nvarchar(250)	INOUT	Prospect
@STATUS	nvarchar(100)	INOUT	Status
@PRIORITY	nvarchar(100)	INOUT	Priority
@REQUESTDATE	date	INOUT	Request date
@DUEDATE	date	INOUT	Due date
@TYPE	nvarchar(100)	INOUT	Research type
@REASON	nvarchar(100)	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
@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

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROSPECTRESEARCHREQUESTCONSTITUENTSUMMARY
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @REQUESTNO nvarchar(100) = null output,
    @CONSTITUENT nvarchar(250) = null output,
    @STATUS nvarchar(100) = null output,
    @PRIORITY nvarchar(100) = null output,
    @REQUESTDATE date = null output,
    @DUEDATE date = null output,
    @TYPE nvarchar(100) = null output,
    @REASON nvarchar(100) = null output,
    @REQUESTEDBYID uniqueidentifier = null output,
    @SUBMITTEDBYID uniqueidentifier = null output,
    @NOTES nvarchar(max) = null output,
    @STATUSCHANGENOTES nvarchar(max) = null output,
    @RESEARCHGROUPID uniqueidentifier = null output,
    @EVENTID uniqueidentifier = null output,
    @RESEARCHGROUPNAME nvarchar(150) = null output,
    @EVENTNAME nvarchar(150) = null output,
    @RECORDTYPECODE tinyint = 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,
        @REQUESTNO = PRRC.REQUESTNO,
        @CONSTITUENT = C.NAME,
        @STATUS = PRRC.STATUS,
        @PRIORITY = PRRP.DESCRIPTION,
        @REQUESTDATE = PRRC.DATEADDED,
        @DUEDATE = PRRC.DUEDATE,
        @TYPE = PRRT.DESCRIPTION,
        @REASON = PRRR.DESCRIPTION,
        @REQUESTEDBYID = PRR.REQUESTEDBYID,
        @SUBMITTEDBYID = PRR.SUBMITTEDBYID,
        @NOTES = case when len(PRRC.NOTES) > 160 then (left(PRRC.NOTES, 160) + '...') else PRRC.NOTES end,
        @STATUSCHANGENOTES = case when len(PRRC.STATUSCHANGENOTES) > 160 then (left(PRRC.STATUSCHANGENOTES, 160) + '...') else PRRC.STATUSCHANGENOTES end,
        @STATUSCHANGEREASON = PRRSR.DESCRIPTION,
        @RESEARCHGROUPID = PRR.RESEARCHGROUPID,
        @EVENTID = PRR.EVENTID,
        @RESEARCHGROUPNAME = RG.NAME,
        @EVENTNAME = E.NAME,
        @RECORDTYPECODE = PRR.RECORDTYPECODE
    from 
        dbo.PROSPECTRESEARCHREQUESTCONSTITUENT PRRC
    join
        dbo.PROSPECTRESEARCHREQUEST PRR on PRR.ID = PRRC.PROSPECTRESEARCHREQUESTID
    left join
        dbo.PROSPECTRESEARCHREQUESTPRIORITYCODE PRRP on PRRP.ID = PRRC.PROSPECTRESEARCHREQUESTPRIORITYCODEID
    left join
        dbo.PROSPECTRESEARCHREQUESTTYPECODE PRRT on PRRT.ID = PRRC.PROSPECTRESEARCHREQUESTTYPECODEID
    left join
        dbo.PROSPECTRESEARCHREQUESTREASONCODE PRRR on PRRR.ID = PRRC.PROSPECTRESEARCHREQUESTREASONCODEID
    left join
        dbo.CONSTITUENT C on C.ID = PRRC.CONSTITUENTID
    left join
        dbo.PROSPECTRESEARCHREQUESTSTATUSCHANGECODE PRRSR on PRRSR.ID = PRRC.PROSPECTRESEARCHREQUESTSTATUSCHANGECODEID
    left join
        dbo.EVENT E on E.ID = PRR.EVENTID
    left join
        dbo.RESEARCHGROUP RG on RG.ID = PRR.RESEARCHGROUPID
    where PRRC.ID = @ID

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

    return 0;