USP_DATAFORMTEMPLATE_VIEW_PROSPECTPLAN

The load procedure used by the view dataform template "Prospect Plan 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
@PROSPECTPLANSTAGE	nvarchar(100)	INOUT	Current plan stage
@PROSPECTMANAGERFUNDRAISERID	uniqueidentifier	INOUT	PROSPECTMANAGERFUNDRAISERID
@PRIMARYMANAGERFUNDRAISERID	uniqueidentifier	INOUT	PRIMARYMANAGERFUNDRAISERID
@SECONDARYMANAGERFUNDRAISERID	uniqueidentifier	INOUT	SECONDARYMANAGERFUNDRAISERID
@PROSPECTMANAGER	nvarchar(700)	INOUT	Prospect manager
@PRIMARYMANAGER	nvarchar(700)	INOUT	Primary manager
@SECONDARYMANAGER	nvarchar(700)	INOUT	Secondary manager
@QUALIFIEDOPPORTUNITY	money	INOUT	Total qualified opportunity amount
@PROSPECT	nvarchar(700)	INOUT	Prospect
@PROSPECTPLANTYPE	nvarchar(100)	INOUT	Prospect plan type
@SECONDARYFUNDRAISERS	xml	INOUT	SECONDARYFUNDRAISERS
@PROSPECTPLAN_PARTICIPANTS	xml	INOUT	PROSPECTPLAN_PARTICIPANTS
@PRIMARYMANAGERSTARTDATE	date	INOUT	Start date
@SECONDARYMANAGERSTARTDATE	date	INOUT	Start date
@PROSPECTMANAGERSTARTDATE	date	INOUT	Start date
@BASECURRENCYID	uniqueidentifier	INOUT	BASECURRENCYID
@STARTDATE	datetime	INOUT	Plan start date
@ACCEPTEDOPPORTUNITY	money	INOUT	Accepted opportunity amount
@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
@ISACTIVE	bit	INOUT

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROSPECTPLAN (
                  @ID uniqueidentifier,
                  @DATALOADED bit = 0 output,
                  @SITE nvarchar(1024) = null output,
                  @PROSPECTPLANSTAGE nvarchar(100) = null output,
                  @PROSPECTMANAGERFUNDRAISERID uniqueidentifier = null output,
                  @PRIMARYMANAGERFUNDRAISERID uniqueidentifier = null output,
                  @SECONDARYMANAGERFUNDRAISERID uniqueidentifier = null output,
                  @PROSPECTMANAGER nvarchar(700) = null output,
                  @PRIMARYMANAGER nvarchar(700) = null output,
                  @SECONDARYMANAGER nvarchar(700) = null output,
                  @QUALIFIEDOPPORTUNITY money = null output,
                  @PROSPECT nvarchar(700) = null output,
                  @PROSPECTPLANTYPE nvarchar(100) = null output,
                  @SECONDARYFUNDRAISERS xml = null output,
                  @PROSPECTPLAN_PARTICIPANTS xml = null output,
                  @PRIMARYMANAGERSTARTDATE date = null output,
                  @SECONDARYMANAGERSTARTDATE date = null output,
                  @PROSPECTMANAGERSTARTDATE date = null output,
                  @BASECURRENCYID uniqueidentifier = null output,
                  @STARTDATE datetime = null output,
                  @ACCEPTEDOPPORTUNITY money = 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,
                  @ISACTIVE bit = null output
                ) as begin
                  set nocount on;

                  set @DATALOADED = 0;

                  declare @CURRENTDATEEARLIESTTIME datetime;
                  set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());


                  select 
                    @DATALOADED = 1,            
                    @SITE = dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID),
                    @PROSPECTPLANSTAGE = dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID),
                    @PROSPECTMANAGERFUNDRAISERID = case when P.PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else P.PROSPECTMANAGERFUNDRAISERID end,
                    @PRIMARYMANAGERFUNDRAISERID = case when PP.PRIMARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else PP.PRIMARYMANAGERFUNDRAISERID end,
                    @SECONDARYMANAGERFUNDRAISERID = case when PP.SECONDARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else PP.SECONDARYMANAGERFUNDRAISERID end,
                    @PROSPECTMANAGER = case when P.PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else PROSPECTMANAGER_NF.NAME end,
                    @PRIMARYMANAGER = case when PP.PRIMARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else PM_NF.NAME end, 
                    @SECONDARYMANAGER = case when PP.SECONDARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else PF_NF.NAME end ,
                    @PROSPECT = PC_NF.NAME,
                    @PROSPECTPLANTYPE = PPTC.DESCRIPTION,
                    @SECONDARYFUNDRAISERS = dbo.UFN_PROSPECTPLAN_GETSECONDARYFUNDRAISERS_TOITEMLISTXML(PP.ID, 'Current'),
                    @PROSPECTPLAN_PARTICIPANTS = dbo.UFN_PROSPECTPLAN_PARTICIPANTS_TOITEMLISTXML(PP.ID),
                    @PRIMARYMANAGERSTARTDATE = case when PP.PRIMARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else PP.PRIMARYMANAGERSTARTDATE end,
                    @SECONDARYMANAGERSTARTDATE = case when PP.SECONDARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else PP.SECONDARYMANAGERSTARTDATE end, 
                    @PROSPECTMANAGERSTARTDATE =  case when P.PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else P.PROSPECTMANAGERSTARTDATE end,
                    @BASECURRENCYID = PP.BASECURRENCYID,
                    @STARTDATE = PP.STARTDATE,
                    @ISACTIVE = PP.ISACTIVE
                  from
                    dbo.PROSPECTPLAN PP
                    inner join dbo.PROSPECTPLANTYPECODE PPTC on PPTC.ID = PP.PROSPECTPLANTYPECODEID
                    left outer join dbo.PROSPECT P on P.ID=PP.PROSPECTID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(P.PROSPECTMANAGERFUNDRAISERID) PROSPECTMANAGER_NF
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PRIMARYMANAGERFUNDRAISERID) PM_NF
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.SECONDARYMANAGERFUNDRAISERID) PF_NF
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PROSPECTID) PC_NF
                  where
                            PP.ID=@ID;

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

                  select
                    @ACCEPTEDOPPORTUNITY = sum(OPPORTUNITY.AMOUNT)
                  from dbo.OPPORTUNITY
                  where OPPORTUNITY.STATUSCODE = 3
                    and OPPORTUNITY.PROSPECTPLANID=@ID

                  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