USP_DATAFORMTEMPLATE_VIEW_STEWARDSHIPPLANSUMMARY

The load procedure used by the view dataform template "Stewardship Plan Summary View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CONSTITUENTNAME	nvarchar(154)	INOUT	CONSTITUENTNAME
@PLANID	uniqueidentifier	INOUT	PLANID
@MANAGERID	uniqueidentifier	INOUT	MANAGERID
@MANAGER	nvarchar(154)	INOUT	Manager
@STEWARDS	xml	INOUT	STEWARDS
@TOTALGIVING	money	INOUT	Total revenue
@RECENTGIFTS	xml	INOUT	Recent Revenue
@BOARDMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	BOARDMEMBERCONSTITUENCYTEXT
@RELATIONCONSTITUENCYTEXT	nvarchar(100)	INOUT	RELATIONCONSTITUENCYTEXT
@STAFFCONSTITUENCYTEXT	nvarchar(100)	INOUT	STAFFCONSTITUENCYTEXT
@DONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	DONORCONSTITUENCYTEXT
@FUNDRAISERCONSTITUENCYTEXT	nvarchar(100)	INOUT	FUNDRAISERCONSTITUENCYTEXT
@PROSPECTCONSTITUENCYTEXT	nvarchar(100)	INOUT	PROSPECTCONSTITUENCYTEXT
@VOLUNTEERCONSTITUENCYTEXT	nvarchar(100)	INOUT	VOLUNTEERCONSTITUENCYTEXT
@COMMUNITYMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	COMMUNITYMEMBERCONSTITUENCYTEXT
@USERDEFINEDCONSTITUENCYTEXT	nvarchar(4000)	INOUT	USERDEFINEDCONSTITUENCYTEXT
@ALUMNUSSTATUSTEXT	nvarchar(100)	INOUT	ALUMNUSSTATUSTEXT
@REGISTRANTSTATUSTEXT	nvarchar(100)	INOUT	REGISTRANTSTATUSTEXT
@VENDORSTATUSTEXT	nvarchar(100)	INOUT	VENDORSTATUSTEXT
@MATCHFINDERCONSTITUENCYTEXT	nvarchar(100)	INOUT	MATCHFINDERCONSTITUENCYTEXT
@MATCHFINDERONLINERECORDID	int	INOUT	MATCHFINDERONLINERECORDID
@MEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	MEMBERCONSTITUENCYTEXT
@RECOGNITIONCONSTITUENCYTEXT	nvarchar(100)	INOUT	RECOGNITIONCONSTITUENCYTEXT
@BANKCONSTITUENCYTEXT	nvarchar(100)	INOUT	BANKCONSTITUENCYTEXT
@COMMITTEECONSTITUENCYTEXT	nvarchar(100)	INOUT	COMMITTEECONSTITUENCYTEXT
@COMMITTEEMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	COMMITTEEMEMBERCONSTITUENCYTEXT
@PLANNEDGIVERCONSTITUENCYTEXT	nvarchar(100)	INOUT	PLANNEDGIVERCONSTITUENCYTEXT
@GRANTORCONSTITUENCYTEXT	nvarchar(100)	INOUT	GRANTORCONSTITUENCYTEXT
@SPONSORCONSTITUENCYTEXT	nvarchar(100)	INOUT	SPONSORCONSTITUENCYTEXT
@LOYALDONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	LOYALDONORCONSTITUENCYTEXT
@MAJORDONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	MAJORDONORCONSTITUENCYTEXT
@PLANOWNERID	uniqueidentifier	INOUT	PLANOWNERID
@SITESLIST	nvarchar(500)	INOUT	Site
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@STARTDATE	datetime	INOUT	Plan start date
@MANAGERSTARTDATE	date	INOUT	Start date
@ISACTIVE	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_STEWARDSHIPPLANSUMMARY (
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @DATALOADED bit = 0 output,
    @CONSTITUENTNAME nvarchar(154) = null output,
    @PLANID uniqueidentifier = null output,
    @MANAGERID uniqueidentifier = null output,
    @MANAGER nvarchar(154) = null output,
    @STEWARDS xml = null output,                    
    @TOTALGIVING money = null output,                   
    @RECENTGIFTS xml = null output, 
    @BOARDMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @RELATIONCONSTITUENCYTEXT nvarchar(100) = null output,
    @STAFFCONSTITUENCYTEXT nvarchar(100) = null output,
    @DONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @FUNDRAISERCONSTITUENCYTEXT nvarchar(100) = null output,
    @PROSPECTCONSTITUENCYTEXT nvarchar(100) = null output,
    @VOLUNTEERCONSTITUENCYTEXT nvarchar(100) = null output,
    @COMMUNITYMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @USERDEFINEDCONSTITUENCYTEXT nvarchar(4000) = null output,
    @ALUMNUSSTATUSTEXT nvarchar(100) = null output,
    @REGISTRANTSTATUSTEXT nvarchar(100) = null output,
    @VENDORSTATUSTEXT nvarchar(100) = null output,
    @MATCHFINDERCONSTITUENCYTEXT nvarchar(100) = null output,
    @MATCHFINDERONLINERECORDID int = null output,
    @MEMBERCONSTITUENCYTEXT nvarchar(100)=null output,
    @RECOGNITIONCONSTITUENCYTEXT nvarchar(100)=null output,
    @BANKCONSTITUENCYTEXT nvarchar(100) = null output,
    @COMMITTEECONSTITUENCYTEXT nvarchar(100) = null output,
    @COMMITTEEMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @PLANNEDGIVERCONSTITUENCYTEXT nvarchar(100)=null output,
    @GRANTORCONSTITUENCYTEXT nvarchar(100) = null output,
    @SPONSORCONSTITUENCYTEXT nvarchar(100) = null output,
    @LOYALDONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @PLANOWNERID uniqueidentifier = null output,
    @SITESLIST nvarchar(500) = null output,
    @BASECURRENCYID uniqueidentifier = null output,
    @STARTDATE datetime = null output,
    @MANAGERSTARTDATE date = null output,
    @ISACTIVE bit = null output
) as begin
    set nocount on;

    set @DATALOADED = 0;
    declare @featureTypeForm tinyint = 1;
    declare @thisFormInstanceId uniqueidentifier = '60BA49E1-E8DD-4c30-B28C-E6E7534D3649';

    select 
        @DATALOADED = 1,
        @MANAGERID = STEWARDSHIPPLAN.MANAGERID,
        @PLANID = STEWARDSHIPPLAN.ID,
        @MANAGER = NF_MANAGER.NAME,
        @STEWARDS = dbo.UFN_STEWARDSHIPPLAN_TOPNSTEWARDS_TOITEMLISTXML(@ID, 0, 4), --get back 4 (we only display 3) so the more link will appear

        @RECENTGIFTS = dbo.UFN_STEWARDSHIPPLAN_RECENTGIFTS2_TOITEMLISTXML(STEWARDSHIPPLAN.CONSTITUENTID, @CURRENTAPPUSERID, @thisFormInstanceId, @featureTypeForm),
        @CONSTITUENTNAME = NF_CONSTITUENT.NAME,
        @PLANOWNERID = STEWARDSHIPPLAN.CONSTITUENTID,
        @SITESLIST = dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLAN.ID),
        @BASECURRENCYID = STEWARDSHIPPLAN.BASECURRENCYID,
        @STARTDATE = STEWARDSHIPPLAN.STARTDATE,
        @MANAGERSTARTDATE = STEWARDSHIPPLAN.MANAGERSTARTDATE,
        @ISACTIVE = STEWARDSHIPPLAN.ISACTIVE
    from
        dbo.STEWARDSHIPPLAN
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.MANAGERID) NF_MANAGER
        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.CONSTITUENTID) NF_CONSTITUENT
    where
        STEWARDSHIPPLAN.ID = @ID


    -- Total Giving

    Select
        @TOTALGIVING = SUM(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @BASECURRENCYID))
    from dbo.REVENUE
    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
    where 
        REVENUE.CONSTITUENTID = @PLANOWNERID    
        and exists
        (
            select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
            where
                RSSUB.REVENUEID = REVENUE.ID  
                -- Using a case statement since the standard site extension filters

                -- resulted in a poor plan

                and
                (case
                    when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
                    when exists (
                        select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @thisFormInstanceId, @featureTypeForm) 
                        where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1  
                    else 0
                 end) = 1
        )
        and --JamesWill 2009-02-20 Work Item 25331 Only include certain types of revenue records

        (
            (REVENUE.TRANSACTIONTYPECODE in (1, 4)) --Pledges, Planned Gifts

            or
            (REVENUE.TRANSACTIONTYPECODE= 0 and 
                (
                    REVENUESPLIT.APPLICATIONCODE in (0, 1, 3, 4, 5, 7, 8, 13) --Donations, Event registration payment, Recurring gift payment, Other, Membership payment, Matching gift payment, grant payment

                )
            )
        );

  exec dbo.USP_CONSTITUENT_GETCONSTITUENCYTEXT
    @CONSTITUENTID = @PLANOWNERID,
    @CURRENTAPPUSERID = @CURRENTAPPUSERID,
    @MATCHFINDERONLINERECORDID = @MATCHFINDERONLINERECORDID output,
    @RECOGNITIONCONSTITUENCYTEXT = @RECOGNITIONCONSTITUENCYTEXT output,
    @MEMBERCONSTITUENCYTEXT = @MEMBERCONSTITUENCYTEXT output,
    @BOARDMEMBERCONSTITUENCYTEXT = @BOARDMEMBERCONSTITUENCYTEXT output,
    @RELATIONCONSTITUENCYTEXT = @RELATIONCONSTITUENCYTEXT output,
    @STAFFCONSTITUENCYTEXT = @STAFFCONSTITUENCYTEXT output,
    @DONORCONSTITUENCYTEXT = @DONORCONSTITUENCYTEXT output,
    @LOYALDONORCONSTITUENCYTEXT = @LOYALDONORCONSTITUENCYTEXT output,
    @MAJORDONORCONSTITUENCYTEXT = @MAJORDONORCONSTITUENCYTEXT output,
    @FUNDRAISERCONSTITUENCYTEXT = @FUNDRAISERCONSTITUENCYTEXT output,
    @PROSPECTCONSTITUENCYTEXT = @PROSPECTCONSTITUENCYTEXT output,
    @VOLUNTEERCONSTITUENCYTEXT = @VOLUNTEERCONSTITUENCYTEXT output,
    @COMMUNITYMEMBERCONSTITUENCYTEXT = @COMMUNITYMEMBERCONSTITUENCYTEXT output,
    @ALUMNUSSTATUSTEXT = @ALUMNUSSTATUSTEXT output,
    @REGISTRANTSTATUSTEXT = @REGISTRANTSTATUSTEXT output,
    @VENDORSTATUSTEXT = @VENDORSTATUSTEXT output,
    @BANKCONSTITUENCYTEXT = @BANKCONSTITUENCYTEXT output,
    @COMMITTEECONSTITUENCYTEXT = @COMMITTEECONSTITUENCYTEXT output,
    @COMMITTEEMEMBERCONSTITUENCYTEXT = @COMMITTEEMEMBERCONSTITUENCYTEXT output,
    @PLANNEDGIVERCONSTITUENCYTEXT = @PLANNEDGIVERCONSTITUENCYTEXT output,
    @SPONSORCONSTITUENCYTEXT = @SPONSORCONSTITUENCYTEXT output,
    @GRANTORCONSTITUENCYTEXT = @GRANTORCONSTITUENCYTEXT output,
    @MATCHFINDERCONSTITUENCYTEXT = @MATCHFINDERCONSTITUENCYTEXT output,
    @USERDEFINEDCONSTITUENCYTEXT = @USERDEFINEDCONSTITUENCYTEXT output;

    return 0;
end