USP_DATAFORMTEMPLATE_VIEW_PROSPECTPLANLIST

The load procedure used by the view dataform template "Prospect Plan List 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.
@SITE	nvarchar(1024)	INOUT	Site
@CURRENTSTAGE	nvarchar(100)	INOUT	Current stage
@LASTDATE	datetime	INOUT	Last step date
@LASTSTEPOWNER	nvarchar(700)	INOUT	Last step owner
@LASTSTEPOBJECTIVE	nvarchar(100)	INOUT	Last step objective
@NEXTDATE	datetime	INOUT	Next step date
@NEXTSTEPOWNER	nvarchar(700)	INOUT	Next step owner
@NEXTSTEPOBJECTIVE	nvarchar(100)	INOUT	Next step objective
@PRIMARYMANAGER	nvarchar(700)	INOUT	Primary manager
@SECONDARYMANAGER	nvarchar(700)	INOUT	Secondary manager
@OPPORTUNITYAMOUNT	money	INOUT	Qualified opportunity amount
@DESIGNATIONS	nvarchar(1000)	INOUT	Designations
@ASKDATE	datetime	INOUT	Ask date
@NAME	nvarchar(100)	INOUT	Plan name
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@UNQUALIFIEDCOUNT	int	INOUT
@UNQUALIFIEDAMOUNT	money	INOUT
@UNQUALIFIEDID	uniqueidentifier	INOUT
@QUALIFIEDCOUNT	int	INOUT
@QUALIFIEDAMOUNT	money	INOUT
@QUALIFIEDID	uniqueidentifier	INOUT
@RESPONSEPENDINGCOUNT	int	INOUT
@RESPONSEPENDINGAMOUNT	money	INOUT
@RESPONSEPENDINGID	uniqueidentifier	INOUT
@ACCEPTEDCOUNT	int	INOUT
@ACCEPTEDAMOUNT	money	INOUT
@ACCEPTEDID	uniqueidentifier	INOUT
@CANCELEDCOUNT	int	INOUT
@CANCELEDAMOUNT	money	INOUT
@CANCELEDID	uniqueidentifier	INOUT
@REJECTEDCOUNT	int	INOUT
@REJECTEDAMOUNT	money	INOUT
@REJECTEDID	uniqueidentifier	INOUT

Definition

Copy



        CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROSPECTPLANLIST
        (
          @ID uniqueidentifier,
          @DATALOADED bit = 0 output,
          @SITE nvarchar(1024) = null output,
          @CURRENTSTAGE nvarchar(100) = null output,
          @LASTDATE datetime = null output,
          @LASTSTEPOWNER nvarchar(700) = null output,
          @LASTSTEPOBJECTIVE nvarchar(100) = null output,
          @NEXTDATE datetime = null output,
          @NEXTSTEPOWNER nvarchar(700) = null output,
          @NEXTSTEPOBJECTIVE nvarchar(100) = null output,
          @PRIMARYMANAGER nvarchar(700) = null output,
          @SECONDARYMANAGER nvarchar(700) = null output,
          @OPPORTUNITYAMOUNT money = null output,
          @DESIGNATIONS nvarchar(1000) = null output,
          @ASKDATE datetime = null output,
          @NAME nvarchar(100) = null output,
          @BASECURRENCYID uniqueidentifier = null output,
          @UNQUALIFIEDCOUNT int = null output,
          @UNQUALIFIEDAMOUNT money = null output,
          @UNQUALIFIEDID uniqueidentifier = null output,
          @QUALIFIEDCOUNT int = null output,
          @QUALIFIEDAMOUNT money = null output,
          @QUALIFIEDID uniqueidentifier = null output,
          @RESPONSEPENDINGCOUNT int = null output,
          @RESPONSEPENDINGAMOUNT money = null output,
          @RESPONSEPENDINGID uniqueidentifier = null output,
          @ACCEPTEDCOUNT int = null output,
          @ACCEPTEDAMOUNT money = null output,
          @ACCEPTEDID uniqueidentifier = null output,
          @CANCELEDCOUNT int = null output,
          @CANCELEDAMOUNT money = null output,
          @CANCELEDID uniqueidentifier = null output,
          @REJECTEDCOUNT int = null output,
          @REJECTEDAMOUNT money = null output,
          @REJECTEDID uniqueidentifier = null output
        )
        as begin
          set nocount on;

          set @DATALOADED = 0;
          declare @CURRENTEARLIESTDATE date = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

          select
            @DATALOADED = 1,
            @SITE = dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID),
            @CURRENTSTAGE = PSC.DESCRIPTION,
            @LASTDATE = LI.ACTUALDATE,
            @LASTSTEPOWNER = LASTSTEPOWNER_NF.NAME,
            @LASTSTEPOBJECTIVE = LI.OBJECTIVE,
            @NEXTDATE = NI.EXPECTEDDATE,
            @NEXTSTEPOWNER = NEXTSTEPOWNER_NF.NAME,
            @NEXTSTEPOBJECTIVE = NI.OBJECTIVE,
            @PRIMARYMANAGER =
              case
                when PP.PRIMARYMANAGERENDDATE < @CURRENTEARLIESTDATE then null
                else PRIMARYMANAGER_NF.NAME
              end,
            @SECONDARYMANAGER =
              case
                when PP.SECONDARYMANAGERENDDATE < @CURRENTEARLIESTDATE then null
                else SECONDARYMANAGER_NF.NAME
              end,
            @NAME = PP.NAME,
            @BASECURRENCYID = PP.BASECURRENCYID
          from dbo.PROSPECTPLAN PP
            left outer join dbo.INTERACTION LI on LI.ID=dbo.UFN_PROSPECTPLAN_GETLASTSTEP(PP.ID)
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(LI.FUNDRAISERID) LASTSTEPOWNER_NF
            left outer join dbo.INTERACTION NI on NI.ID=dbo.UFN_PROSPECTPLAN_GETNEXTSTEP(PP.ID)
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(NI.FUNDRAISERID) NEXTSTEPOWNER_NF
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PRIMARYMANAGERFUNDRAISERID) PRIMARYMANAGER_NF
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.SECONDARYMANAGERFUNDRAISERID) SECONDARYMANAGER_NF
            left outer join dbo.PROSPECTPLANTYPECODE STC on STC.ID=PP.PROSPECTPLANTYPECODEID
            left outer join dbo.PROSPECTPLANSTATUSCODE PSC on PSC.ID=PP.PROSPECTPLANSTATUSCODEID
          where PP.ID = @ID;

          select @OPPORTUNITYAMOUNT = sum(OPPORTUNITY.AMOUNT)
          from dbo.OPPORTUNITY
          where
            OPPORTUNITY.STATUSCODE in (1,2,3)
            and OPPORTUNITY.PROSPECTPLANID = @ID;

          select distinct @DESIGNATIONS = dbo.UDA_BUILDLIST(DESIGNATIONS.NAME)
          from (
            select distinct dbo.UFN_DESIGNATION_BUILDNAME(OPPORTUNITYDESIGNATION.DESIGNATIONID) as NAME
            from dbo.OPPORTUNITY
              inner join dbo.OPPORTUNITYDESIGNATION on OPPORTUNITYDESIGNATION.OPPORTUNITYID = OPPORTUNITY.ID
            where
              OPPORTUNITY.STATUSCODE in (1,2,3)
              and OPPORTUNITY.PROSPECTPLANID=@ID
          ) as DESIGNATIONS;

          exec dbo.USP_PROSPECTPLAN_GETOPPORTUNITYSUMMARY
            @ID,
            @UNQUALIFIEDCOUNT output,
            @UNQUALIFIEDAMOUNT output,
            @QUALIFIEDCOUNT output,
            @QUALIFIEDAMOUNT output,
            @RESPONSEPENDINGCOUNT output,
            @RESPONSEPENDINGAMOUNT output,
            @ACCEPTEDCOUNT output,
            @ACCEPTEDAMOUNT output,
            @REJECTEDCOUNT output,
            @REJECTEDAMOUNT output,
            @CANCELEDCOUNT output,
            @CANCELEDAMOUNT output,
            @UNQUALIFIEDID output,
            @QUALIFIEDID output,
            @RESPONSEPENDINGID output,
            @ACCEPTEDID output,
            @REJECTEDID output,
            @CANCELEDID output;

          return 0;
        end