USP_DATAFORMTEMPLATE_VIEW_STEWARDSHIPPLANDETAIL

The load procedure used by the view dataform template "Stewardship Plan Detail 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.
@MANAGER	nvarchar(154)	INOUT	Plan manager
@TOTALGIVING	money	INOUT	Total giving
@STEWARDS	xml	INOUT	STEWARDS
@RECENTGIFTS	xml	INOUT	RECENTGIFTS
@PURPOSES	xml	INOUT	PURPOSES
@PLANNAME	nvarchar(154)	INOUT	Plan name
@SITESLIST	nvarchar(500)	INOUT	Site
@BASECURRENCYID	uniqueidentifier	INOUT	BASECURRENCYID

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_STEWARDSHIPPLANDETAIL (
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @MANAGER nvarchar(154) = null output,
    @TOTALGIVING money = null output,
    @STEWARDS xml = null output,
    @RECENTGIFTS xml = null output, 
    @PURPOSES xml = null output,
    @PLANNAME nvarchar(154) = null output,
    @SITESLIST nvarchar(500) = null output,
    @BASECURRENCYID uniqueidentifier = null output  
) as begin
    set nocount on;

    set @DATALOADED = 0;

    select 
        @DATALOADED = 1,
        @MANAGER = NF_MANAGER.NAME,                     
        @STEWARDS = dbo.UFN_STEWARDSHIPPLAN_TOP5STEWARDS_TOITEMLISTXML(@ID, 0),
        @RECENTGIFTS = dbo.UFN_STEWARDSHIPPLAN_RECENTGIFTS_TOITEMLISTXML(@ID, @CURRENTAPPUSERID),
        @PURPOSES = dbo.UFN_STEWARDSHIPPLAN_TOP4PURPOSES_TOITEMLISTXML(@ID, @CURRENTAPPUSERID),
        @PLANNAME = STEWARDSHIPPLAN.NAME,
        @SITESLIST = dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLAN.ID),
        @BASECURRENCYID = STEWARDSHIPPLAN.BASECURRENCYID
    from
        dbo.STEWARDSHIPPLAN
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.MANAGERID) NF_MANAGER
    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 = @ID       
        and
        (
            (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, donor challenge

                )
            )
        )   

    return 0;

end