USP_DATAFORMTEMPLATE_VIEW2_OPPORTUNITY

The load procedure used by the view dataform template "Opportunity View Form 2"

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.
@PROSPECTPLANSTAGE	nvarchar(100)	INOUT	Current stage
@PROSPECTMANAGERFUNDRAISERID	uniqueidentifier	INOUT	PROSPECTMANAGERFUNDRAISERID
@PRIMARYMANAGERFUNDRAISERID	uniqueidentifier	INOUT	PRIMARYMANAGERFUNDRAISERID
@SECONDARYMANAGERFUNDRAISERID	uniqueidentifier	INOUT	SECONDARYMANAGERFUNDRAISERID
@PROSPECTMANAGER	nvarchar(154)	INOUT	Prospect manager
@PRIMARYMANAGER	nvarchar(154)	INOUT	Primary manager
@SECONDARYMANAGER	nvarchar(154)	INOUT	Secondary manager
@ADDRESS	nvarchar(300)	INOUT	Address
@PHONE	nvarchar(100)	INOUT	Phone number
@EMAIL	UDT_EMAILADDRESS	INOUT	Email address
@URL	UDT_WEBADDRESS	INOUT	Web address
@PRIMARYMANAGERSTARTDATE	date	INOUT	Start date
@SECONDARYMANAGERSTARTDATE	date	INOUT	Start date
@PROSPECTMANAGERSTARTDATE	date	INOUT	Start date

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW2_OPPORTUNITY (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @PROSPECTPLANSTAGE nvarchar(100) = null output,
                    @PROSPECTMANAGERFUNDRAISERID uniqueidentifier = null output,
                    @PRIMARYMANAGERFUNDRAISERID uniqueidentifier = null output,
                    @SECONDARYMANAGERFUNDRAISERID uniqueidentifier = null output,
                    @PROSPECTMANAGER nvarchar(154) = null output,
                    @PRIMARYMANAGER nvarchar(154) = null output,
                    @SECONDARYMANAGER nvarchar(154) = null output,
                    @ADDRESS nvarchar(300) = null output,
                    @PHONE nvarchar(100) = null output,
                    @EMAIL dbo.UDT_EMAILADDRESS = null output,
                    @URL dbo.UDT_WEBADDRESS = null output,
                    @PRIMARYMANAGERSTARTDATE date = null output,
                    @SECONDARYMANAGERSTARTDATE date = null output,
                    @PROSPECTMANAGERSTARTDATE date = null output
                    ) as begin
                    set nocount on;

                    set @DATALOADED = 0;

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

                    --Don't select expired managers' details

                    select 
                        @DATALOADED = 1,
                        @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 NF_PROSPECTMANAGER.NAME end,
                        @PRIMARYMANAGER = case when PP.PRIMARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else NF_PM.NAME end,
                        @SECONDARYMANAGER = case when PP.SECONDARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else NF_PF.NAME end,
                        @ADDRESS = dbo.UFN_BUILDFULLADDRESS(A.ID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID),
                        @PHONE = PH.NUMBER,
                        @EMAIL = E.EMAILADDRESS,
                        @URL = PC.WEBADDRESS,
                        @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
                    from
                        dbo.OPPORTUNITY O
                        inner join dbo.PROSPECTPLAN PP on PP.ID=O.PROSPECTPLANID
                        left outer join dbo.PROSPECT P on P.ID=PP.PROSPECTID
                        left outer join dbo.CONSTITUENT PC on PC.ID=PP.PROSPECTID
                        left outer join dbo.ADDRESS A on A.CONSTITUENTID = PC.ID and A.ISPRIMARY = 1
                        left outer join dbo.PHONE PH on PH.CONSTITUENTID = PC.ID and PH.ISPRIMARY = 1
                        left outer join dbo.EMAILADDRESS E on E.CONSTITUENTID = PC.ID and E.ISPRIMARY = 1
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(P.PROSPECTMANAGERFUNDRAISERID) NF_PROSPECTMANAGER
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PRIMARYMANAGERFUNDRAISERID) NF_PM
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.SECONDARYMANAGERFUNDRAISERID) NF_PF
                    where
                        O.ID=@ID;                                  

                    return 0;

                end