USP_DATAFORMTEMPLATE_VIEW_HOUSEHOLDPLANNEDGIFTSUMMARY

The load procedure used by the view dataform template "Household Planned Gifts Summary View"

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.
@HOUSEHOLDNUMBERPROPOSED	int	INOUT	Number proposed
@HOUSEHOLDNUMBERPENDINGRESPONSE	int	INOUT	Number pending response
@HOUSEHOLDNUMBERACCEPTED	int	INOUT	Number accepted
@HOUSEHOLDAMOUNTACCEPTED	money	INOUT	Amount accepted
@MEMBERNUMBERPROPOSED	int	INOUT	Number proposed
@MEMBERNUMBERPENDINGRESPONSE	int	INOUT	Number pending response
@MEMBERNUMBERACCEPTED	int	INOUT	Number accepted
@MEMBERAMOUNTACCEPTED	money	INOUT	Amount accepted
@TOTALNUMBERACCEPTED	int	INOUT	Total number accepted
@TOTALAMOUNTACCEPTED	money	INOUT	Total amount accepted
@CURRENCYID	uniqueidentifier	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_HOUSEHOLDPLANNEDGIFTSUMMARY
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @HOUSEHOLDNUMBERPROPOSED int = null output,
                    @HOUSEHOLDNUMBERPENDINGRESPONSE int = null output,
                    @HOUSEHOLDNUMBERACCEPTED int = null output,
                    @HOUSEHOLDAMOUNTACCEPTED money = null output,
                    @MEMBERNUMBERPROPOSED int = null output,
                    @MEMBERNUMBERPENDINGRESPONSE int = null output,
                    @MEMBERNUMBERACCEPTED int = null output,
                    @MEMBERAMOUNTACCEPTED money = null output,
                    @TOTALNUMBERACCEPTED int = null output,
                    @TOTALAMOUNTACCEPTED money = null output,
                    @CURRENCYID uniqueidentifier = null output,
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as begin
                    set nocount on;

                    --Bug 215326 Show amounts in the app user's base currency

                    set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());  

                    -- household proposed

                    set @HOUSEHOLDNUMBERPROPOSED = 0;
                    select
                        @HOUSEHOLDNUMBERPROPOSED = count(PG.ID)
                    from
                        dbo.PLANNEDGIFT PG
                    where
                        PG.CONSTITUENTID = @ID
                    and
                        PG.STATUSCODE = 0;

                    -- household pending response

                    set @HOUSEHOLDNUMBERPENDINGRESPONSE = 0;
                    select
                        @HOUSEHOLDNUMBERPENDINGRESPONSE = count(PG.ID)
                    from
                        dbo.PLANNEDGIFT PG
                    where
                        PG.CONSTITUENTID = @ID
                    and
                        PG.STATUSCODE = 1;

                    -- household accepted

                    set @HOUSEHOLDNUMBERACCEPTED = 0;
                    set @HOUSEHOLDAMOUNTACCEPTED = cast(0 as money);
                    select
                        @HOUSEHOLDNUMBERACCEPTED = count(PG.ID),
                        @HOUSEHOLDAMOUNTACCEPTED = sum(dbo.UFN_PLANNEDGIFTDESIGNATION_AMOUNTINCURRENCY(PG.ID, @CURRENCYID))
                    from
                        dbo.PLANNEDGIFT PG
                    where
                        PG.CONSTITUENTID = @ID
                    and
                        PG.STATUSCODE = 2;

                    -- member proposed

                    set @MEMBERNUMBERPROPOSED = 0;
                    select
                        @MEMBERNUMBERPROPOSED = count(PG.ID)
                    from
                        dbo.GROUPMEMBER GM
                    inner join
                        dbo.PLANNEDGIFT PG on PG.CONSTITUENTID = GM.MEMBERID
                    left outer join
                        dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                    where
                        GM.GROUPID = @ID
                    and
                        PG.STATUSCODE = 0
                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= @CURRENTDATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                        or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO >= @CURRENTDATE));

                    -- member pending response

                    set @MEMBERNUMBERPENDINGRESPONSE = 0;
                    select
                        @MEMBERNUMBERPENDINGRESPONSE = count(PG.ID)
                    from
                        dbo.GROUPMEMBER GM
                    inner join
                        dbo.PLANNEDGIFT PG on PG.CONSTITUENTID = GM.MEMBERID
                    left outer join
                        dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                    where
                        GM.GROUPID = @ID
                    and
                        PG.STATUSCODE = 1
                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= @CURRENTDATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                        or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO >= @CURRENTDATE));

                    -- member accepted

                    set @MEMBERNUMBERACCEPTED = 0;
                    set @MEMBERAMOUNTACCEPTED = cast(0 as money);
                    select
                        @MEMBERNUMBERACCEPTED = count(PG.ID),
                        @MEMBERAMOUNTACCEPTED = sum(dbo.UFN_PLANNEDGIFTDESIGNATION_AMOUNTINCURRENCY(PG.ID, @CURRENCYID))
                    from
                        dbo.GROUPMEMBER GM
                    inner join
                        dbo.PLANNEDGIFT PG on PG.CONSTITUENTID = GM.MEMBERID
                    left outer join
                        dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                    where
                        GM.GROUPID = @ID
                    and
                        PG.STATUSCODE = 2
                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= @CURRENTDATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                        or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO >= @CURRENTDATE));

                    select
                        @DATALOADED = 1,
                        @TOTALNUMBERACCEPTED = @HOUSEHOLDNUMBERACCEPTED + @MEMBERNUMBERACCEPTED,
                        @TOTALAMOUNTACCEPTED = coalesce(@HOUSEHOLDAMOUNTACCEPTED,0) + coalesce(@MEMBERAMOUNTACCEPTED,0);
                end