USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMGDETAILS

The load procedure used by the view dataform template "Constituent Matching Gift Details View Form"
Parameters

Parameter	Parameter Type	Mode	Description
@ID	nchar	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.
@MATCHESGIFTS	bit	INOUT	Matches gifts
@CONSTITUENTMATCHEDANNUALTOTAL	money	INOUT	Constituent annual matched total
@CONSTITUENTMATCHEDTOTAL	money	INOUT	Constituent matched total
@CONSTITUENTGIFTTOTAL	money	INOUT	Constituent donated total
@CONSTITUENTGIFTANNUALTOTAL	money	INOUT	Constituent annual donated total
@ORGANIZATIONHASMATCHEDGIFT	bit	INOUT	Organization has matched gift
@SPLITS	xml	INOUT	Designations
@MATCHINGGIFTCONDITION	xml	INOUT	Matching gift condition
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMGDETAILS
            (
                @ID nchar(72),    
                @DATALOADED bit = 0 output,
                @MATCHESGIFTS bit = null output,
                @CONSTITUENTMATCHEDANNUALTOTAL money = null output,
                @CONSTITUENTMATCHEDTOTAL money = null output,
                @CONSTITUENTGIFTTOTAL money = null output,
                @CONSTITUENTGIFTANNUALTOTAL money = null output,
                @ORGANIZATIONHASMATCHEDGIFT bit = null output,
                @SPLITS xml = null output,
                @MATCHINGGIFTCONDITION xml = null output,
                @CURRENTAPPUSERID uniqueidentifier = null
            ) as
                set nocount on;

                set @DATALOADED = 0;

                declare @MATCHINGORGANIZATIONID uniqueidentifier;
                declare @MATCHEDREVENUEID uniqueidentifier;
                declare @CONSTITUENTID uniqueidentifier;
                declare @MGCOUNT int;
                declare @DATE datetime;

                begin try
                    set @MATCHINGORGANIZATIONID = convert(uniqueidentifier,substring(@ID,0,37));
                    set @MATCHEDREVENUEID = convert(uniqueidentifier,substring(@ID,37,37));
                    set @DATALOADED = 1;
                end try
                begin catch
                    set @DATALOADED = 0;
                end catch

                if @DATALOADED = 1 begin
                    set @DATALOADED = 0;

                    if exists (select 1 from dbo.CONSTITUENT where ID = @MATCHINGORGANIZATIONID)
                    begin
                        -- Check security for this constituent.  Using UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT

                        -- since this form's security is implied through other forms.

                        declare @ISADMIN bit;
                        declare @APPUSER_IN_NONRACROLE bit;
                        declare @APPUSER_IN_NOSECGROUPROLE bit;

                        set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

                        if @ISADMIN = 0
                            set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);

                        if @ISADMIN = 0 and @APPUSER_IN_NONRACROLE = 0
                            set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

                        if
                        (
                            @ISADMIN = 0
                            and @APPUSER_IN_NONRACROLE = 0
                            and (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, @MATCHINGORGANIZATIONID, @APPUSER_IN_NOSECGROUPROLE) = 0)
                        )
                            raiserror('BBERR_DB_RECORDSECURITY_PERMISSION_DENIED', 13, 1);
                    end

                    select
                        @CONSTITUENTID = REVENUE.CONSTITUENTID,
                        @DATE = DATE
                    from
                        dbo.REVENUE
                    where
                        REVENUE.ID = @MATCHEDREVENUEID;

                    declare @FISCALYEARSTARTDATE datetime;
                    set @FISCALYEARSTARTDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@DATE, 0);

                    select
                        @MATCHESGIFTS = 1 
                    from
                        dbo.RELATIONSHIP R
                    where
                        R.RELATIONSHIPCONSTITUENTID = @MATCHINGORGANIZATIONID and
                        R.RECIPROCALCONSTITUENTID = @CONSTITUENTID and
                        R.ISMATCHINGGIFTRELATIONSHIP = 1;

                    if @@ROWCOUNT = 0 begin
                        set @MATCHESGIFTS = 0;
                    end

                    if @CONSTITUENTID is not null
                        select
                            @CONSTITUENTMATCHEDANNUALTOTAL = dbo.UFN_CONSTITUENT_GETMATCHEDTOTALFORYEAR(ID, @MATCHINGORGANIZATIONID, @FISCALYEARSTARTDATE),
                            @CONSTITUENTMATCHEDTOTAL = dbo.UFN_CONSTITUENT_GETMATCHEDTOTAL(ID, @MATCHINGORGANIZATIONID),
                            @CONSTITUENTGIFTANNUALTOTAL = dbo.UFN_CONSTITUENT_GETGIFTTOTALFORYEAR(ID, @FISCALYEARSTARTDATE),
                            @CONSTITUENTGIFTTOTAL = dbo.UFN_CONSTITUENT_GETGIFTTOTAL(ID)
                        from
                            dbo.CONSTITUENT
                        where
                            ID = @CONSTITUENTID;
                    else
                    begin
                        set @CONSTITUENTMATCHEDANNUALTOTAL = 0;
                        set @CONSTITUENTMATCHEDTOTAL = 0;
                        set @CONSTITUENTGIFTANNUALTOTAL = 0;
                        set @CONSTITUENTGIFTTOTAL = 0;
                    end

                    select
                        @MGCOUNT = count(REVENUE.ID)
                    from
                        dbo.REVENUE 
                    inner join dbo.REVENUEMATCHINGGIFT RMG on REVENUE.ID = RMG.ID
                    where
                        RMG.MGSOURCEREVENUEID = @MATCHEDREVENUEID and
                        REVENUE.CONSTITUENTID = @MATCHINGORGANIZATIONID;

                    if @MGCOUNT > 0 
                        set @ORGANIZATIONHASMATCHEDGIFT  = 1; 
                    else
                        set @ORGANIZATIONHASMATCHEDGIFT = 0;


                    --Update the IDs in the SPLITS since they are currently a copy of the IDs on the original gift

                    declare @SPLITSTABLE table
                    (
                        AMOUNT money,
                        DESIGNATIONID uniqueidentifier
                    );

                    insert into @SPLITSTABLE(AMOUNT, DESIGNATIONID)
                        select sum(AMOUNT), DESIGNATIONID
                        from dbo.UFN_REVENUE_GETSPLITS(@MATCHEDREVENUEID)
                        where TYPECODE = 0
                        group by DESIGNATIONID

                    select @SPLITS = (select newid(), AMOUNT, DESIGNATIONID, 0 as APPLICATIONCODE, 0 as TYPECODE from @SPLITSTABLE for xml raw('ITEM'), type, elements, root('SPLITS'), binary base64)

                    set @MATCHINGGIFTCONDITION = dbo.UFN_MATCHINGGIFTPLEDGE_GETCONDITION_2_TOITEMLISTXML(@MATCHINGORGANIZATIONID, @CONSTITUENTID);

                    if @@ROWCOUNT > 0
                        set @DATALOADED = 1;    
                end

                return 0;