USP_DATAFORMTEMPLATE_VIEW_DESIGNATIONGOAL

The load procedure used by the view dataform template "Designation Goal 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
@GOAL	money	INOUT	Goal
@STARTDATE	datetime	INOUT	Start date
@ENDDATE	datetime	INOUT	End date
@DATERANGE	nvarchar(100)	INOUT	Date Range
@TOTALREVENUE	money	INOUT	Total revenue
@OVERUNDER	money	INOUT	Over (under)
@AVERAGEGIFT	money	INOUT	Average gift
@TOTALGIFTS	int	INOUT	Total gifts

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DESIGNATIONGOAL
                (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @NAME nvarchar(100) = null output,
                @GOAL money = null output,
                @STARTDATE datetime = null output,
                @ENDDATE datetime = null output,
                @DATERANGE nvarchar(100) = null output,
                @TOTALREVENUE money = null output,
                @OVERUNDER money = null output,
                @AVERAGEGIFT money = null output,
                @TOTALGIFTS int = null output
                )
            as
            begin
                set nocount on;

                set @DATALOADED = 0;

                with DESIGNATION_CTE as 
                    (select D.ID
                    from dbo.DESIGNATION D
                        inner join dbo.DESIGNATION D2
                        on ((D.DESIGNATIONLEVEL1ID = D2.DESIGNATIONLEVEL1ID) and
                            (D.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID or D2.DESIGNATIONLEVEL2ID is null) and
                            (D.DESIGNATIONLEVEL3ID = D2.DESIGNATIONLEVEL3ID or D2.DESIGNATIONLEVEL3ID is null) and
                            (D.DESIGNATIONLEVEL4ID = D2.DESIGNATIONLEVEL4ID or D2.DESIGNATIONLEVEL4ID is null) and
                            (D.DESIGNATIONLEVEL5ID = D2.DESIGNATIONLEVEL5ID or D2.DESIGNATIONLEVEL5ID is null))
                        where D2.ID = (select DESIGNATIONID from dbo.DESIGNATIONGOAL where ID = @ID)
                    )

                select
                    @DATALOADED = 1,
                    @NAME = DLG.NAME,
                    @GOAL = DG.GOAL,
                    @STARTDATE = DLG.STARTDATE,
                    @ENDDATE = DLG.ENDDATE,
                    @DATERANGE = ISNULL(CONVERT(nvarchar(10), DLG.STARTDATE, 101) + ' to ' + CONVERT(nvarchar(10), DLG.ENDDATE, 101), ''),                    
                    @TOTALREVENUE = (dbo.UFN_DESIGNATION_GETTOTALRECEIVED(DG.DESIGNATIONID,DLG.STARTDATE,DLG.ENDDATE,1) + 
                                    dbo.UFN_DESIGNATION_GETPLEDGEBALANCE(DG.DESIGNATIONID,DLG.STARTDATE,DLG.ENDDATE,1)),
                    @TOTALGIFTS = coalesce((select count(distinct RD.ID) 
                                                from dbo.REVENUESPLIT as SPLIT 
                                                inner join DESIGNATION_CTE as D on SPLIT.DESIGNATIONID = D.ID
                                                inner join dbo.REVENUE as RD on SPLIT.REVENUEID = RD.ID                                                
                                                where (RD.DATE between DLG.STARTDATE and DLG.ENDDATE) and
                                                        dbo.UFN_REVENUE_HASDESIGNATION(RD.TRANSACTIONTYPECODE, SPLIT.APPLICATIONCODE) = 1
                                            ), 0)
                from
                    dbo.DESIGNATIONGOAL DG
                    inner join dbo.DESIGNATIONLEVELGOAL DLG on DG.DESIGNATIONLEVELGOALID = DLG.ID
                where DG.ID = @ID;

                set @OVERUNDER = @TOTALREVENUE - @GOAL;

                if @TOTALGIFTS > 0
                    set @AVERAGEGIFT = @TOTALREVENUE / @TOTALGIFTS;
                else
                    set @AVERAGEGIFT = 0;

                return 0
            end