USP_DATAFORMTEMPLATE_VIEW_OPPORTUNITY

The load procedure used by the view dataform template "Opportunity 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.
@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
@COMMENT	nvarchar(max)	INOUT	Comment
@STATUS	nvarchar(100)	INOUT	Status
@EXPECTEDASKAMOUNT	money	INOUT	Expected ask amount
@ASKAMOUNT	money	INOUT	Actual ask amount
@ACCEPTEDAMOUNT	money	INOUT	Accepted amount
@EXPECTEDASKDATE	datetime	INOUT	Expected ask date
@ASKDATE	datetime	INOUT	Actual ask date
@RESPONSEDATE	datetime	INOUT	Response date
@TOTALPAIDAMOUNT	money	INOUT	Total paid amount
@LIKELIHOODTYPECODE	nvarchar(100)	INOUT	Likelihood
@OPPORTUNITYTYPE	nvarchar(100)	INOUT	Opportunity type

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_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,
                    @COMMENT nvarchar(max) = null output,
                    @STATUS nvarchar(100) = null output,
                    @EXPECTEDASKAMOUNT money = null output,
                    @ASKAMOUNT money = null output,
                    @ACCEPTEDAMOUNT money = null output,
                    @EXPECTEDASKDATE datetime = null output,
                    @ASKDATE datetime = null output,
                    @RESPONSEDATE datetime = null output,
                    @TOTALPAIDAMOUNT money = null output,
                    @LIKELIHOODTYPECODE nvarchar(100) = null output,
                    @OPPORTUNITYTYPE nvarchar(100) = null output
                    ) as begin
                    set nocount on;

                    set @DATALOADED = 0;

                        select 
                        @DATALOADED = 1,
                        @PROSPECTPLANSTAGE = dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID),
                        @PROSPECTMANAGERFUNDRAISERID = PROSPECTMANAGER.ID,
                        @PRIMARYMANAGERFUNDRAISERID = PM.ID,
                        @SECONDARYMANAGERFUNDRAISERID = PF.ID,
                        @PROSPECTMANAGER = PROSPECTMANAGER.NAME,
                        @PRIMARYMANAGER = PM.NAME,
                        @SECONDARYMANAGER = PF.NAME,
                        @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,
                        @COMMENT = O.COMMENT,
                        @STATUS = O.STATUS,
                        @EXPECTEDASKAMOUNT = O.EXPECTEDASKAMOUNT,
                        @ASKAMOUNT = O.ASKAMOUNT,
                        @ACCEPTEDAMOUNT = case O.STATUSCODE when 3 then O.AMOUNT else 0 end,
                        @EXPECTEDASKDATE = O.EXPECTEDASKDATE,
                        @ASKDATE = O.ASKDATE,
                        @RESPONSEDATE = O.RESPONSEDATE,
                        @LIKELIHOODTYPECODE = dbo.UFN_LIKELIHOODTYPECODE_GETDESCRIPTION(O.LIKELIHOODTYPECODEID),
                        @OPPORTUNITYTYPE = dbo.UFN_OPPORTUNITYTYPECODE_GETDESCRIPTION(O.OPPORTUNITYTYPECODEID)
                    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 PROSPECTMANAGER on PROSPECTMANAGER.ID=P.PROSPECTMANAGERFUNDRAISERID
                        left outer join dbo.CONSTITUENT PM on PM.ID=PP.PRIMARYMANAGERFUNDRAISERID
                        left outer join dbo.CONSTITUENT PF on PF.ID=PP.SECONDARYMANAGERFUNDRAISERID
                        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
                    where
                        O.ID=@ID;

                    set @TOTALPAIDAMOUNT = dbo.UFN_OPPORTUNITY_GETAMOUNTPAID(@ID);                                          

                    return 0;

                end