USP_DATAFORMTEMPLATE_VIEW_APPLICATIONINFO

The load procedure used by the view dataform template "Payment: Application View Information 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.
@APPLIEDTODATE	datetime	INOUT	APPLIEDTODATE
@APPLIEDTOTRANSACTIONTYPECODE	tinyint	INOUT	APPLIEDTOTRANSACTIONTYPECODE
@APPLIEDTOCONSTITUENTID	uniqueidentifier	INOUT	Constituent
@DESIGNATIONS	nvarchar(max)	INOUT	Designations
@CAMPAIGNS	nvarchar(max)	INOUT	Campaigns
@CATEGORYCODEID	uniqueidentifier	INOUT	Revenue category
@DECLINESGIFTAID	bit	INOUT	Declines Gift Aid
@ISGIFTAIDSPONSORSHIP	bit	INOUT	Gift Aid sponsorship

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_APPLICATIONINFO
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @APPLIEDTODATE datetime = null output,
                    @APPLIEDTOTRANSACTIONTYPECODE tinyint = null output,
                    @APPLIEDTOCONSTITUENTID uniqueidentifier = null output,
                    @DESIGNATIONS nvarchar(max) = null output,
                    @CAMPAIGNS nvarchar(max) = null output,
                    @CATEGORYCODEID uniqueidentifier = null output,
                    @DECLINESGIFTAID bit = null output,
          @ISGIFTAIDSPONSORSHIP bit = null output
                )
                as
                set nocount on;

                declare @APPLICATIONCODE tinyint;

                set @DATALOADED = 0;

                select @DATALOADED = 1,
                        @APPLICATIONCODE = REVENUESPLIT.APPLICATIONCODE,
                        @DESIGNATIONS = dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT.DESIGNATIONID),
                        @CATEGORYCODEID = GLREVENUECATEGORYMAPPINGID,
                        @CAMPAIGNS = 
                            (select 
                                dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME) 
                            from 
                                dbo.CAMPAIGN 
                            inner join 
                                dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                            where
                                REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
                            ),
                        @DECLINESGIFTAID = REVENUESPLITGIFTAID.DECLINESGIFTAID,
            @ISGIFTAIDSPONSORSHIP = REVENUESPLITGIFTAID.ISSPONSORSHIP
                from dbo.REVENUESPLIT 
                    left outer join dbo.REVENUECATEGORY on REVENUESPLIT.ID = REVENUECATEGORY.ID
                    left outer join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
                    left outer join dbo.REVENUESPLITGIFTAID on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
                where REVENUESPLIT.ID = @ID;

                if @APPLICATIONCODE = 1 --Event

                begin
                    select 
                        @APPLIEDTODATE = EVENT.STARTDATE,
                        @APPLIEDTOCONSTITUENTID = REGISTRANT.CONSTITUENTID

                    from dbo.EVENTREGISTRANTPAYMENT
                        inner join dbo.REGISTRANT on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
                        inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
                    where EVENTREGISTRANTPAYMENT.PAYMENTID = @ID
                end

                if @APPLICATIONCODE = 3 --RG

                begin
                    select 
                        @APPLIEDTODATE = REVENUE.DATE,
                        @APPLIEDTOTRANSACTIONTYPECODE = REVENUE.TRANSACTIONTYPECODE,
                        @APPLIEDTOCONSTITUENTID = REVENUE.CONSTITUENTID
                    from dbo.RECURRINGGIFTACTIVITY
                        inner join dbo.REVENUE on REVENUE.ID = RECURRINGGIFTACTIVITY.SOURCEREVENUEID
                    where RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = @ID
                end

                if @APPLICATIONCODE = 5 --Membership

                begin
                    select 
                        @APPLIEDTODATE = REVENUE.DATE,
                        @APPLIEDTOTRANSACTIONTYPECODE = REVENUE.TRANSACTIONTYPECODE,
                        @APPLIEDTOCONSTITUENTID = MEMBER.CONSTITUENTID
                    from dbo.REVENUESPLIT 
                        inner join dbo.MEMBERSHIPTRANSACTION on REVENUESPLIT.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                        inner join dbo.MEMBERSHIP on MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
                        inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                        inner join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                    where REVENUESPLIT.ID = @ID
                        and MEMBER.ISPRIMARY = 1
                end

                if @APPLICATIONCODE in (2, 6, 7, 8, 12, 13) --Pledge,MGPledge,PlannedGift, Grant award, Auction purchase, Donor challenge

                begin
                    select 
                        @APPLIEDTODATE = REVENUE.DATE,
                        @APPLIEDTOTRANSACTIONTYPECODE = REVENUE.TRANSACTIONTYPECODE,
                        @APPLIEDTOCONSTITUENTID = REVENUE.CONSTITUENTID
                    from dbo.INSTALLMENTSPLITPAYMENT
                        inner join dbo.REVENUE on REVENUE.ID = INSTALLMENTSPLITPAYMENT.PLEDGEID
                    where INSTALLMENTSPLITPAYMENT.PAYMENTID = @ID
                end

                return 0;