USP_DATAFORMTEMPLATE_VIEW_ADDPROSPECTPLANSPROCESS

The load procedure used by the view dataform template "Add Prospect Plans Process View 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.
@NAME	nvarchar(100)	INOUT	Name
@DESCRIPTION	nvarchar(100)	INOUT	Description
@IDSETREGISTER	nvarchar(300)	INOUT	Constituent selection
@PROSPECTMANAGERFUNDRAISER	nvarchar(154)	INOUT	Manager for new prospects
@PROSPECTPLANNAME	nvarchar(100)	INOUT	Plan name
@PROSPECTPLANTYPE	nvarchar(100)	INOUT	Plan type
@PLANOUTLINE	nvarchar(100)	INOUT	Plan outline
@STARTDATE	nvarchar(50)	INOUT	Start date
@PRIMARYMANAGERFUNDRAISER	nvarchar(154)	INOUT	Primary manager
@SECONDARYMANAGERFUNDRAISER	nvarchar(154)	INOUT	Secondary manager
@SECONDARYFUNDRAISERS	nvarchar(2000)	INOUT	Secondary solicitors
@SITES	nvarchar(2000)	INOUT	Sites
@OPPORTUNITYTYPE	nvarchar(100)	INOUT	Opportunity type
@EXPECTEDASKAMOUNT	money	INOUT	Expected ask amount
@PARAMETERSID	uniqueidentifier	INOUT	PARAMETERSID
@OPPORTUNITYTRANSACTIONCURRENCYID	uniqueidentifier	INOUT	OPPORTUNITYTRANSACTIONCURRENCYID

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ADDPROSPECTPLANSPROCESS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(100) = null output,
    @DESCRIPTION nvarchar(100) = null output,
    @IDSETREGISTER nvarchar(300) = null output,
    @PROSPECTMANAGERFUNDRAISER nvarchar(154) = null output,
    @PROSPECTPLANNAME nvarchar(100) = null output,
    @PROSPECTPLANTYPE nvarchar(100) = null output,
    @PLANOUTLINE nvarchar(100) = null output,
    @STARTDATE nvarchar(50) = null output,
    @PRIMARYMANAGERFUNDRAISER nvarchar(154) = null output,
    @SECONDARYMANAGERFUNDRAISER nvarchar(154) = null output,
    @SECONDARYFUNDRAISERS nvarchar(2000) = null output,
    @SITES nvarchar(2000) = null output,
    @OPPORTUNITYTYPE nvarchar(100) = null output,
    @EXPECTEDASKAMOUNT money = null output,
    @PARAMETERSID uniqueidentifier = null output,
    @OPPORTUNITYTRANSACTIONCURRENCYID uniqueidentifier = null output
)
as
    set nocount on;

    set @DATALOADED = 0;
    set @PARAMETERSID = @ID;

    select @DATALOADED = 1,
           @NAME = ADDPROSPECTPLANSPROCESS.NAME,
           @DESCRIPTION = ADDPROSPECTPLANSPROCESS.DESCRIPTION,
           @IDSETREGISTER = IDSETREGISTER.NAME,
           @PROSPECTMANAGERFUNDRAISER = NF_PRM.NAME,
           @PROSPECTPLANNAME = ADDPROSPECTPLANSPROCESS.PROSPECTPLANNAME,
           @PROSPECTPLANTYPE = PROSPECTPLANTYPECODE.DESCRIPTION,
           @PLANOUTLINE = PLANOUTLINE.NAME,
           @STARTDATE = case ADDPROSPECTPLANSPROCESS.STARTDATETYPECODE
                            when 0 then N'Date this process runs'
                            when 1 then cast(ADDPROSPECTPLANSPROCESS.STARTDATE as nvarchar)
                            when 2 then cast(ADDPROSPECTPLANSPROCESS.DAYSBEFOREORAFTER as nvarchar) + N' days after this process runs'
                            when 3 then cast(ADDPROSPECTPLANSPROCESS.DAYSBEFOREORAFTER as nvarchar) + N' days before this process runs'
                        end,
           @PRIMARYMANAGERFUNDRAISER = NF_PM.NAME,
           @SECONDARYMANAGERFUNDRAISER = NF_SM.NAME,
           @OPPORTUNITYTYPE = OPPORTUNITYTYPECODE.DESCRIPTION,
           @EXPECTEDASKAMOUNT = case CREATEOPPORTUNITIES when 1 then EXPECTEDASKAMOUNT end,
           @OPPORTUNITYTRANSACTIONCURRENCYID = OPPORTUNITYTRANSACTIONCURRENCYID
    from dbo.ADDPROSPECTPLANSPROCESS
    inner join dbo.IDSETREGISTER on IDSETREGISTER.ID = ADDPROSPECTPLANSPROCESS.IDSETREGISTERID
    inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLANTYPECODE.ID = ADDPROSPECTPLANSPROCESS.PROSPECTPLANTYPECODEID
    left join dbo.PLANOUTLINE on PLANOUTLINE.ID = ADDPROSPECTPLANSPROCESS.PLANOUTLINEID
    left join dbo.OPPORTUNITYTYPECODE on OPPORTUNITYTYPECODE.ID = ADDPROSPECTPLANSPROCESS.OPPORTUNITYTYPECODEID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(ADDPROSPECTPLANSPROCESS.PROSPECTMANAGERFUNDRAISERID) NF_PRM
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(ADDPROSPECTPLANSPROCESS.PRIMARYMANAGERFUNDRAISERID) NF_PM
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(ADDPROSPECTPLANSPROCESS.SECONDARYMANAGERFUNDRAISERID) NF_SM
    where ADDPROSPECTPLANSPROCESS.ID = @ID;

    select @SECONDARYFUNDRAISERS = dbo.UDA_BUILDLIST(NAME)
    from (select top 100 NF.NAME
          from dbo.ADDPROSPECTPLANSPROCESSSECONDARYFUNDRAISER
          inner join dbo.CONSTITUENT on CONSTITUENT.ID = ADDPROSPECTPLANSPROCESSSECONDARYFUNDRAISER.FUNDRAISERID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
          where ADDPROSPECTPLANSPROCESSSECONDARYFUNDRAISER.ADDPROSPECTPLANSPROCESSID = @ID
          order by ADDPROSPECTPLANSPROCESSSECONDARYFUNDRAISER.SEQUENCE) X;

    select @SITES = dbo.UDA_BUILDLIST(NAME)
    from (select top 100 SITE.NAME
          from dbo.ADDPROSPECTPLANSPROCESSSITE
          inner join dbo.SITE on SITE.ID = ADDPROSPECTPLANSPROCESSSITE.SITEID
          where ADDPROSPECTPLANSPROCESSSITE.ADDPROSPECTPLANSPROCESSID = @ID
          order by SITE.NAME) X;

    return 0;