USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUP_RECOGNITIONHISTORY

The load procedure used by the view dataform template "Constituent Group Recognition History 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.
@GROUPINCLUDESMEMBERGIVING	bit	INOUT	Group includes member giving
@TOTALNUMBERFROMGROUP	int	INOUT	Total number of recognition credits for group
@TOTALNUMBERFROMGROUPMEMBERS	int	INOUT	Total number of recognition credits for group members
@TOTALGROUPAMOUNT	money	INOUT	Total recognition for group
@TOTALGROUPMEMBERAMOUNT	money	INOUT	Total recognition for group members
@ISHOUSEHOLD	bit	INOUT	Is household
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@TOTALGROUPMEMBERAMOUNTWITHGIFTAID	money	INOUT	Total recognition for members including Gift Aid
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUP_RECOGNITIONHISTORY
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @GROUPINCLUDESMEMBERGIVING bit = null output,
                    @TOTALNUMBERFROMGROUP int = null output,
                    @TOTALNUMBERFROMGROUPMEMBERS int = null output,
                    @TOTALGROUPAMOUNT money = null output,
                    @TOTALGROUPMEMBERAMOUNT money = null output,
                    @ISHOUSEHOLD bit = null output,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @TOTALGROUPMEMBERAMOUNTWITHGIFTAID money = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    declare @SECURITYFEATUREID uniqueidentifier;
                    declare @SECURITYFEATURETYPE tinyint;
                    set @SECURITYFEATUREID = '06169335-5b81-4151-a8f8-00e88c749b4b';
                    set @SECURITYFEATURETYPE = 1;

                    select
                        @GROUPINCLUDESMEMBERGIVING = 
                            case
                                when GD.GROUPTYPECODE = 0 then 1
                                when GD.GROUPTYPECODE = 1 then GT.INCLUDEMEMBERGIVING
                            end,
                        @ISHOUSEHOLD = 
                            case
                                when GD.GROUPTYPECODE = 0 then 1
                                when GD.GROUPTYPECODE = 1 then 0
                            end,
                        @DATALOADED = 1
                    from dbo.GROUPDATA GD
                    left join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
                    where GD.ID = @ID;

                    -- calculate the member recognitions

                    if @GROUPINCLUDESMEMBERGIVING = 1
                    begin
                        select
                            @TOTALNUMBERFROMGROUPMEMBERS = count(RR.ID),
                            @TOTALGROUPMEMBERAMOUNT = cast(sum(cast(RR.AMOUNT as decimal(20,5))) as money),
                            @TOTALGROUPMEMBERAMOUNTWITHGIFTAID = sum(case when RS.BASEAMOUNT > 0 then
                                                                        (case R.TYPECODE 
                                                                            when 0 then case when RR.AMOUNT > RS.BASEAMOUNT then dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT(RS.ID, 1) + RR.AMOUNT else RR.AMOUNT/RS.BASEAMOUNT * dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT(RS.ID, 1) + RR.AMOUNT end
                                                                            when 1 then case when RR.AMOUNT > RS.BASEAMOUNT then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT(RS.ID) - RS.BASEAMOUNT + RR.AMOUNT else RR.AMOUNT/RS.BASEAMOUNT * (dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT(RS.ID) - RS.BASEAMOUNT) + RR.AMOUNT end
                                                                            else RR.AMOUNT end)
                                                                    else RS.BASEAMOUNT end)
                        from
                            dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS(1, null, null) RR
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
                            inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
                            inner join dbo.GROUPMEMBER GM on RR.CONSTITUENTID = GM.MEMBERID
                            left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                            left join dbo.RECOGNITIONCREDIT on RR.ID = RECOGNITIONCREDIT.ID
                        where 
                            GM.GROUPID = @ID 
                            and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= RR.EFFECTIVEDATE))
                            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= RR.EFFECTIVEDATE)) 
                            or (GMDR.DATEFROM <= RR.EFFECTIVEDATE and GMDR.DATETO >= RR.EFFECTIVEDATE))
                            and R.DELETEDON is null
                            and RS.DELETEDON is null
                            and RS.TYPECODE <> 1
                            and exists
                            (
                                select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVSITES
                                where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
                                    or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)  
                                        where ((RECOGNITIONCREDIT.ID is null) and UFN_SITESFORUSERONFEATURE.SITEID = REVSITES.SITEID)
                                        or (UFN_SITESFORUSERONFEATURE.SITEID=dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID))
                                        ))
                            )
                    end

                    -- calculate the recognitions of the group itself

                    select
                        @TOTALNUMBERFROMGROUP = count(RR.ID),
                        @TOTALGROUPAMOUNT = cast(sum(cast(RR.AMOUNT as decimal(20,5))) as money)
                    from
                        dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS(1, null, null) RR
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID                    
                        inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
                        left join dbo.RECOGNITIONCREDIT on RR.ID = RECOGNITIONCREDIT.ID
                    where 
                        RR.CONSTITUENTID = @ID
                        and R.DELETEDON is null
                        and RS.DELETEDON is null
                        and RS.TYPECODE <> 1
                        and exists
                        (
                            select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVSITES
                            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
                                or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)   
                                        where ((RECOGNITIONCREDIT.ID is null) and UFN_SITESFORUSERONFEATURE.SITEID = REVSITES.SITEID)
                                        or (UFN_SITESFORUSERONFEATURE.SITEID=dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID))
                                        ))
                        );

                    select @TOTALGROUPMEMBERAMOUNT = coalesce(@TOTALGROUPMEMBERAMOUNT, 0);
                    select @TOTALGROUPAMOUNT = coalesce(@TOTALGROUPAMOUNT, 0);
                    select @TOTALNUMBERFROMGROUP = coalesce(@TOTALNUMBERFROMGROUP, 0);
                    select @TOTALNUMBERFROMGROUPMEMBERS = coalesce(@TOTALNUMBERFROMGROUPMEMBERS, 0);
                    select @TOTALGROUPMEMBERAMOUNTWITHGIFTAID = coalesce(@TOTALGROUPMEMBERAMOUNTWITHGIFTAID, 0);

                    return 0;