USP_DATAFORMTEMPLATE_VIEW_STEWARDSHIPPLANSTEPSUMMARY

The load procedure used by the view dataform template "Stewardship Plan Step 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
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent ID
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_STEWARDSHIPPLANSTEPSUMMARY
(
  @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,
  @CONSTITUENTID uniqueidentifier = null output,
  @BASECURRENCYID uniqueidentifier = null output
)
as begin
  set nocount on;
  set @DATALOADED = 0;
  declare @featureTypeForm tinyint = 1;
  declare @thisFormInstanceId uniqueidentifier = '53DCD1DE-4771-4140-8622-002A308C11F7';

  select 
    @DATALOADED = 1,
    @PLANID = STEWARDSHIPPLANSTEP.PLANID,
    @MANAGERID = STEWARDSHIPPLAN.MANAGERID,
    @MANAGER = NF_MANAGER.NAME,                     
    @STEWARDS = dbo.UFN_STEWARDSHIPPLAN_TOP5STEWARDS_TOITEMLISTXML(STEWARDSHIPPLANSTEP.PLANID, 0),
    @RECENTGIFTS = dbo.UFN_STEWARDSHIPPLAN_RECENTGIFTS2_TOITEMLISTXML(STEWARDSHIPPLAN.CONSTITUENTID, @CURRENTAPPUSERID, @thisFormInstanceId, @featureTypeForm),
    @CONSTITUENTNAME = NF_CONSTITUENT.NAME,
    @CONSTITUENTID = STEWARDSHIPPLAN.CONSTITUENTID,
    @BASECURRENCYID = STEWARDSHIPPLAN.BASECURRENCYID
  from
    dbo.STEWARDSHIPPLANSTEP
  inner join
    dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEWARDSHIPPLANSTEP.PLANID
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.MANAGERID) NF_MANAGER
  cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.CONSTITUENTID) NF_CONSTITUENT
  where
    STEWARDSHIPPLANSTEP.ID = @ID;

  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 = @CONSTITUENTID
    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
  (
    (REVENUE.TRANSACTIONTYPECODE in (1, 4))
    or
      (REVENUE.TRANSACTIONTYPECODE = 0
      and 
        (REVENUESPLIT.APPLICATIONCODE in (0, 1, 3, 4, 5, 7, 8, 13))
    )
  );

  exec dbo.USP_CONSTITUENT_GETCONSTITUENCYTEXT
    @CONSTITUENTID = @CONSTITUENTID,
    @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