USP_DATAFORMTEMPLATE_VIEW_REVENUETRANSACTIONDETAIL

The load procedure used by the view dataform template "Revenue Transaction Detail 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.
@DESIGNATION	nvarchar(100)	INOUT	Designation
@CAMPAIGNS	nvarchar(max)	INOUT	Campaign
@APPLIEDID	uniqueidentifier	INOUT	Applied ID
@APPLIEDDATE	datetime	INOUT	Applied date
@APPLIEDTYPE	nvarchar(100)	INOUT	Applied type
@APPLIEDNAME	nvarchar(700)	INOUT	Applied to
@OPPORTUNITYID	uniqueidentifier	INOUT	Opportunity ID
@OPPORTUNITYNAME	nvarchar(500)	INOUT	Associated with
@APPLIEDTOCOUNT	int	INOUT	APPLIEDTOCOUNT
@CATEGORYDESCRIPTION	nvarchar(100)	INOUT	Revenue category
@APPLICATIONCODE	tinyint	INOUT	Application code
@SOLICITORS	xml	INOUT	SOLICITORS
@RECOGNITIONS	xml	INOUT	RECOGNITIONS
@DONATIONFORPLANNEDGIFT	bit	INOUT	DONATIONFORPLANNEDGIFT
@GIFTAIDQUALIFICATIONSTATUS	nvarchar(30)	INOUT	Gift Aid qualification status
@TAXCLAIMELIGIBILITY	nvarchar(20)	INOUT	TAXCLAIMELIGIBILITY
@TAXCLAIMAMOUNT	money	INOUT	Tax claim amount
@TAXCLAIMNUMBER	nvarchar(10)	INOUT	Tax claim number
@PREVIOUSCLAIMAMOUNT	money	INOUT	Previous tax claim amount
@PREVIOUSCLAIMNUMBER	nvarchar(10)	INOUT	Previous tax claim number
@SHOWPOTENTIAL	tinyint	INOUT	Show gift aid as potential
@CONSTITUENTISGROUP	bit	INOUT	Constituent is a group
@SHOWREFUNDDETAILS	bit	INOUT	Show refund details for application
@TAXCLAIMELIGIBILITYSTATUSCODE	tinyint	INOUT	Tax claim eligibility
@AUCTIONITEMPACKAGENAME	nvarchar(100)	INOUT	Auction package
@AUCTIONITEMPACKAGEID	uniqueidentifier	INOUT	AUCTIONITEMPACKAGEID
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency ID
@ISPLANNEDGIFTADDITION	bit	INOUT	Is planned gift addition
@DONORCHALLENGEREVENUEID	uniqueidentifier	INOUT	DONORCHALLENGEREVENUEID
@DONORCHALLENGEAMOUNT	money	INOUT	DONORCHALLENGEAMOUNT
@DONORCHALLENGETRANSACTIONCURRENCYID	uniqueidentifier	INOUT	DONORCHALLENGETRANSACTIONCURRENCYID
@DONORCHALLENGETYPE	nvarchar(100)	INOUT	DONORCHALLENGETYPE
@DONORCHALLENGECONSTITUENT	nvarchar(100)	INOUT	DONORCHALLENGECONSTITUENT
@DONORCHALLENGETEXT	nvarchar(100)	INOUT	DONORCHALLENGETEXT
@APPLIEDRECIPRICOLCONSTITUENTNAME	nvarchar(400)	INOUT	Applied reciprocal constituent name
@RECIPRICOLLOOKUPID	nvarchar(100)	INOUT	Reciprocal applied lookup ID
@DONORCHALLENGECLAIMS	xml	INOUT	DONORCHALLENGECLAIMS
@DONORCHALLENGEREVENUE2ID	uniqueidentifier	INOUT	DONORCHALLENGEREVENUE2ID
@DONORCHALLENGEREVENUE3ID	uniqueidentifier	INOUT	DONORCHALLENGEREVENUE3ID
@DONORCHALLENGETEXT2	nvarchar(100)	INOUT	DONORCHALLENGETEXT2
@DONORCHALLENGETEXT3	nvarchar(100)	INOUT	DONORCHALLENGETEXT3
@GIVENTO	nvarchar(100)	INOUT
@QUANTITY	tinyint	INOUT
@DESCRIPTION	nvarchar(100)	INOUT
@LINKEDTOMEMBERSHIP	bit	INOUT
@MEMBERSHIPID	uniqueidentifier	INOUT
@MEMBERSHIPNAME	nvarchar(100)	INOUT
@MEMBERSHIPDATE	datetime	INOUT
@MEMBERNAME	nvarchar(700)	INOUT
@MEMBERSHIPADDON	nvarchar(100)	INOUT
@ISNOTGRANTPAYMENT	bit	INOUT
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REVENUETRANSACTIONDETAIL
                (
                  @ID uniqueidentifier, 
                  @DATALOADED bit = 0 output,
                  @DESIGNATION nvarchar(100) = null output,
                  @CAMPAIGNS nvarchar(max) = null output,
                  @APPLIEDID uniqueidentifier = null output,
                  @APPLIEDDATE datetime = null output,
                  @APPLIEDTYPE nvarchar(100) = null output,
                  @APPLIEDNAME nvarchar(700) = null output,
                  @OPPORTUNITYID uniqueidentifier = null output,
                  @OPPORTUNITYNAME nvarchar(500) = null output,         
                  @APPLIEDTOCOUNT integer = null output,
                  @CATEGORYDESCRIPTION nvarchar(100) = null output,
                  @APPLICATIONCODE tinyint = null output,
                  @SOLICITORS xml = null output,
                  @RECOGNITIONS xml = null output,
                  @DONATIONFORPLANNEDGIFT bit = null output,
                  @GIFTAIDQUALIFICATIONSTATUS nvarchar(30) = null output,
                  -- @TAXCLAIMELIGIBILITY is deprecated.  Should use @TAXCLAIMELIGIBILITYSTATUSCODE instead.

                  @TAXCLAIMELIGIBILITY nvarchar(20) = null output,
                  @TAXCLAIMAMOUNT money = null output,
                  @TAXCLAIMNUMBER nvarchar(10) = null output,
                  @PREVIOUSCLAIMAMOUNT money = null output,
                  @PREVIOUSCLAIMNUMBER nvarchar(10) = null output,
                  @SHOWPOTENTIAL tinyint = null output,
                  @CONSTITUENTISGROUP bit = null output,
                  @SHOWREFUNDDETAILS bit = null output,
                  @TAXCLAIMELIGIBILITYSTATUSCODE tinyint = null output,
                  @AUCTIONITEMPACKAGENAME nvarchar(100) = null output,
                  @AUCTIONITEMPACKAGEID uniqueidentifier = null output,
                  @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                  @ISPLANNEDGIFTADDITION bit = null output,
                  @DONORCHALLENGEREVENUEID uniqueidentifier = null output,
                  @DONORCHALLENGEAMOUNT money = null output,
                  @DONORCHALLENGETRANSACTIONCURRENCYID uniqueidentifier = null output,
                  @DONORCHALLENGETYPE nvarchar(100) = null output,
                  @DONORCHALLENGECONSTITUENT nvarchar(100) = null output,
                  @DONORCHALLENGETEXT nvarchar(100) = null output,
                  @APPLIEDRECIPRICOLCONSTITUENTNAME nvarchar(400) = null output,
                  @RECIPRICOLLOOKUPID nvarchar(100) = null output,
                  @DONORCHALLENGECLAIMS xml = null output,
                  @DONORCHALLENGEREVENUE2ID uniqueidentifier = null output,
                  @DONORCHALLENGEREVENUE3ID uniqueidentifier = null output,
                  @DONORCHALLENGETEXT2 nvarchar(100) = null output,
                  @DONORCHALLENGETEXT3 nvarchar(100) = null output,
                  @GIVENTO nvarchar(100) = null output,
                  @QUANTITY tinyint = null output,
                  @DESCRIPTION nvarchar(100) = null output,
                  @LINKEDTOMEMBERSHIP bit = null output,
                  @MEMBERSHIPID uniqueidentifier = null output,
                  @MEMBERSHIPNAME nvarchar(100) = null output,
                  @MEMBERSHIPDATE datetime = null output,
                  @MEMBERNAME nvarchar(700) = null output,
                  @MEMBERSHIPADDON nvarchar(100) = null output,
                  @ISNOTGRANTPAYMENT bit = null output                 
                )
                as

                set nocount on;

                set @DATALOADED = 0;

                set @LINKEDTOMEMBERSHIP = 0;

                declare @REVENUEID uniqueidentifier;
                declare @REVENUETYPECODE tinyint;
                declare @REVENUETYPE nvarchar(20);
                select
                  @REVENUEID = FINANCIALTRANSACTION.ID,
                  @DATALOADED = 1,
                  @APPLICATIONCODE = REVENUESPLIT_EXT.APPLICATIONCODE,
                  @DESIGNATION = DESIGNATION.NAME,
                  @CATEGORYDESCRIPTION = coalesce(GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME, ''),
                  @SOLICITORS = dbo.UFN_REVENUE_GETSOLICITORS_2_TOITEMLISTXML(@ID),
                  @RECOGNITIONS = dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS_TOITEMLISTXML(@ID),
                  @REVENUETYPECODE = REVENUESPLIT_EXT.TYPECODE,
                  @REVENUETYPE = REVENUESPLIT_EXT.TYPE,
                  @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                  --@DONORCHALLENGEREVENUEID = (select top 1 MATCHEDREVENUEID from dbo.DONORCHALLENGEENCUMBERED where DONORCHALLENGEENCUMBERED.REVENUESPLITID = @ID and STATUSTYPECODE = 1),

                  @DONORCHALLENGECLAIMS = dbo.UFN_REVENUETRANSACTION_TOP3DONORCHALLENGECLAIMS_TOITEMLISTXML(@ID)
                  ,@ISNOTGRANTPAYMENT = case REVENUESPLIT_EXT.APPLICATIONCODE when 8 then 0 else 1 end    
                from dbo.FINANCIALTRANSACTION
                inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                left outer join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
                left join dbo.REVENUECATEGORY
                  on FINANCIALTRANSACTIONLINEITEM.ID = REVENUECATEGORY.ID
                left join dbo.GLREVENUECATEGORYMAPPING
                  on REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID
                where FINANCIALTRANSACTIONLINEITEM.ID = @ID
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1

                select @DONORCHALLENGEREVENUEID = @DONORCHALLENGECLAIMS.value('(/DONORCHALLENGECLAIMS/ITEM[1]/DONORCHALLENGEREVENUEID)[1]','uniqueidentifier')
                select @DONORCHALLENGEREVENUE2ID = @DONORCHALLENGECLAIMS.value('(/DONORCHALLENGECLAIMS/ITEM[2]/DONORCHALLENGEREVENUEID)[1]','uniqueidentifier')
                select @DONORCHALLENGEREVENUE3ID = @DONORCHALLENGECLAIMS.value('(/DONORCHALLENGECLAIMS/ITEM[3]/DONORCHALLENGEREVENUEID)[1]','uniqueidentifier')             

                select @CONSTITUENTISGROUP = (ISGROUP|ISORGANIZATION)
                from dbo.CONSTITUENT
                  inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
                where FINANCIALTRANSACTION.ID = @REVENUEID
                    and FINANCIALTRANSACTION.DELETEDON is null;

                select
                  @CAMPAIGNS = dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
                from dbo.REVENUESPLITCAMPAIGN
                inner join dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
                where REVENUESPLITCAMPAIGN.REVENUESPLITID = @ID

                set @DONATIONFORPLANNEDGIFT = 0
                if @APPLICATIONCODE = 0 -- Donations that came from planned gifts

                begin

                  if exists (select ID from dbo.MEMBERSHIPTRANSACTION where REVENUESPLITID = @ID)
                  begin
                    select top 1
                        @APPLIEDID = MEMBERSHIP.ID,
                        @APPLIEDDATE = MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
                        @APPLIEDTYPE = dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID),
                        @APPLIEDNAME = NF.NAME,
                        @GIVENTO = case
                                      when MEMBERSHIP.GIVENBYID is null then null
                                      else NF.NAME
                                   end,
                        @LINKEDTOMEMBERSHIP = 1
                    from dbo.MEMBERSHIPTRANSACTION
                      inner join dbo.MEMBERSHIP on MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
                      inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID) NF
                    where MEMBERSHIPTRANSACTION.REVENUESPLITID = @ID and MEMBER.ISPRIMARY = 1;
                  end
                  else
                  begin
                      select top 1
                        @DONATIONFORPLANNEDGIFT = 1,
                        @APPLIEDID = PLANNEDGIFTREVENUESPLIT.PLANNEDGIFTID,
                        @APPLIEDDATE = PLANNEDGIFT.GIFTDATE,
                        @APPLIEDNAME = NF.NAME
                      from dbo.PLANNEDGIFTREVENUESPLIT
                      inner join dbo.PLANNEDGIFT
                        on PLANNEDGIFT.ID = PLANNEDGIFTREVENUESPLIT.PLANNEDGIFTID
                      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PLANNEDGIFT.CONSTITUENTID) NF
                      where PLANNEDGIFTREVENUESPLIT.REVENUESPLITID = @ID
                  end

                end

                if @APPLICATIONCODE = 2 or @APPLICATIONCODE = 8 or @APPLICATIONCODE = 19 --pledge payment or grant award or membership installment plan

                  select top 1
                    @APPLIEDID = FINANCIALTRANSACTION.ID,
                    @APPLIEDDATE  = FINANCIALTRANSACTION.DATE,
                    @APPLIEDTYPE = FINANCIALTRANSACTION.TYPE,
                    @APPLIEDNAME = NF.NAME
                  from dbo.INSTALLMENTSPLITPAYMENT
                  inner join dbo.FINANCIALTRANSACTION
                    on INSTALLMENTSPLITPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID
                  inner join dbo.REVENUE_EXT
                    on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) NF
                  where PAYMENTID = @ID
                    and FINANCIALTRANSACTION.DELETEDON is null

                if @APPLICATIONCODE = 19
                begin
                    if @REVENUETYPECODE <> 18
                        select top 1 @MEMBERSHIPID = MEMBERSHIPTRANSACTION.MEMBERSHIPID,
                                        @MEMBERSHIPDATE = MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
                                        @MEMBERSHIPNAME = MEMBERSHIPPROGRAM.NAME,
                                        @MEMBERNAME = NF.NAME
                        from dbo.INSTALLMENTSPLITPAYMENT
                        inner join dbo.FINANCIALTRANSACTION on INSTALLMENTSPLITPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID
                        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                        inner join dbo.MEMBERSHIPTRANSACTION on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                        inner join dbo.MEMBERSHIP on MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
                        inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                        inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID) NF
                        where FINANCIALTRANSACTION.DELETEDON is null
                            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                            and INSTALLMENTSPLITPAYMENT.PAYMENTID = @ID
                            and MEMBER.ISPRIMARY = 1
                    else
                        select top 1 @MEMBERSHIPID = MEMBERSHIPTRANSACTION.MEMBERSHIPID,
                                        @MEMBERSHIPADDON = ADDON.NAME
                        from dbo.INSTALLMENTSPLITPAYMENT
                        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                        inner join dbo.MEMBERSHIPADDON on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPADDON.REVENUESPLITID
                        inner join dbo.ADDON on MEMBERSHIPADDON.ADDONID = ADDON.ID
                        inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
                        where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                            and INSTALLMENTSPLITPAYMENT.PAYMENTID = @ID
                end

                if @APPLICATIONCODE = 3 --recurring gift payment

                begin

                    set @LINKEDTOMEMBERSHIP = 0
                    select
                        @LINKEDTOMEMBERSHIP = 1
                    from
                        dbo.RECURRINGGIFTACTIVITY
                            inner join dbo.FINANCIALTRANSACTION on RECURRINGGIFTACTIVITY.SOURCEREVENUEID = FINANCIALTRANSACTION.ID
                            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                            inner join dbo.MEMBERSHIPTRANSACTION on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                    where
                        PAYMENTREVENUEID = @ID
                        and FINANCIALTRANSACTION.DELETEDON is null
                        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                        and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1

                    select top 1
                        @APPLIEDID = case @LINKEDTOMEMBERSHIP
                                when 0 then FINANCIALTRANSACTION.ID
                                else MEMBERSHIPTRANSACTION.MEMBERSHIPID
                                end,
                        @APPLIEDDATE = cast(FINANCIALTRANSACTION.DATE as datetime),
                        @APPLIEDTYPE = case @REVENUETYPECODE
                                        when 9 then @REVENUETYPE + ' ' + lower(FINANCIALTRANSACTION.TYPE) + ' (' +
                                        (select dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID)
                                        from dbo.SPONSORSHIPPAYMENT
                                        inner join dbo.SPONSORSHIP on SPONSORSHIP.ID = SPONSORSHIPPAYMENT.SPONSORSHIPID
                                        where SPONSORSHIPPAYMENT.ID = @ID) + ')'
                                        when 17 then 'Sponsorship recurring additional gift '+ '('+
                                        (select top 1 dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID)
                                        from dbo.SPONSORSHIPRECURRINGADDITIONALGIFT SPRAG
                                        inner join dbo.SPONSORSHIP on SPONSORSHIP.ID = SPRAG.SPONSORSHIPID
                                        where SPRAG.REVENUEID = FINANCIALTRANSACTION.ID
                                        order by case when SPONSORSHIP.STATUSCODE = 1 then 1 else 2 end, SPONSORSHIP.STARTDATE desc, SPONSORSHIP.DATECHANGED desc) + ')'
                                        when 2 then
                                            case @LINKEDTOMEMBERSHIP 
                                                when 1 then 'Recurring membership payment '
                                                else FINANCIALTRANSACTION.TYPE end
                                        else FINANCIALTRANSACTION.TYPE
                                        end,
                        @APPLIEDNAME = NF.NAME
                    from dbo.RECURRINGGIFTACTIVITY
                    inner join dbo.FINANCIALTRANSACTION on RECURRINGGIFTACTIVITY.SOURCEREVENUEID = FINANCIALTRANSACTION.ID
                    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    left join dbo.MEMBERSHIPTRANSACTION on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) NF
                    where RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = @ID
                        and FINANCIALTRANSACTION.DELETEDON is null
                        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                        and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1

                end

                if @APPLICATIONCODE = 1 --Event Fee payment

                  select top 1
                    @APPLIEDID = EVENTREGISTRANTPAYMENT.REGISTRANTID,
                    @APPLIEDTYPE = EVENT.NAME,
                    @APPLIEDNAME = NF.NAME
                  from dbo.EVENTREGISTRANTPAYMENT
                  inner join dbo.REGISTRANT
                    on EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID
                  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) NF
                  inner join dbo.[EVENT]
                    on REGISTRANT.EVENTID = [EVENT].ID
                  where PAYMENTID = @ID;

                if @APPLICATIONCODE = 18 and @REVENUETYPECODE = 18 --Membership add-on

                begin
                    select
                        @APPLIEDID = MEMBERSHIPID,
                        @APPLIEDNAME = NAME,
                                @LINKEDTOMEMBERSHIP = 1,
                        @DESCRIPTION = DESCRIPTION,
                        @QUANTITY = QUANTITY
                    from
                        dbo.MEMBERSHIPADDON
                            inner join dbo.ADDON on MEMBERSHIPADDON.ADDONID = ADDON.ID
                            inner join dbo.MEMBERSHIPPROGRAMADDON on MEMBERSHIPPROGRAMADDON.ADDONID = ADDON.ID
                    where
                        MEMBERSHIPADDON.REVENUESPLITID = @ID
                end

                if @APPLICATIONCODE = 5 and @REVENUETYPECODE = 2 --Membership

                begin
                    set @LINKEDTOMEMBERSHIP = 1

                  select top 1
                    @APPLIEDID = MEMBERSHIP.ID,
                    @APPLIEDDATE = MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
                    @APPLIEDTYPE = dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID),
                    @APPLIEDNAME = NF.NAME,
                    @GIVENTO = case
                                  when MEMBERSHIP.GIVENBYID is null then null
                                  else NF.NAME
                               end
                  from dbo.MEMBERSHIPTRANSACTION
                  inner join dbo.MEMBERSHIP on MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
                  inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID) NF
                  where MEMBERSHIPTRANSACTION.REVENUESPLITID = @ID and MEMBER.ISPRIMARY = 1;

                  if @APPLIEDID is null  -- The membership was refunded

                  begin
                    select top 1
                      @APPLIEDID = MEMBER.ID,
                      @APPLIEDTYPE = dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID),
                      @APPLIEDNAME = NF.NAME
                    from
                      dbo.CREDITITEM
                    inner join
                      dbo.CREDITITEMMEMBERSHIP on CREDITITEMMEMBERSHIP.ID = CREDITITEM.ID
                    inner join
                      dbo.MEMBERSHIP ON MEMBERSHIP.ID = CREDITITEMMEMBERSHIP.MEMBERSHIPID
                    inner join
                      dbo.MEMBER on MEMBER.MEMBERSHIPID = CREDITITEMMEMBERSHIP.MEMBERSHIPID
                    outer apply
                      dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID) NF
                    where
                      CREDITITEM.REVENUESPLITID = @ID
                      and MEMBER.ISPRIMARY = 1;
                  end
                end

                if @APPLICATIONCODE = 7 or @APPLICATIONCODE = 17 --Matching gift payment or pending gift payment

                begin
                  select @APPLIEDTOCOUNT = count(*) 
                  from dbo.INSTALLMENTSPLITPAYMENT
                  inner join dbo.REVENUE
                    on INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID
                  inner join dbo.CONSTITUENT
                    on REVENUE.CONSTITUENTID = CONSTITUENT.ID
                  where PAYMENTID = @ID

                  if @APPLIEDTOCOUNT = 1
                  begin
                    select top 1
                      @APPLIEDID = REVENUE.ID,
                      @APPLIEDDATE  = REVENUE.DATE,
                      @APPLIEDTYPE = REVENUE.TRANSACTIONTYPE,
                      @APPLIEDNAME = NF.NAME
                    from dbo.INSTALLMENTSPLITPAYMENT
                    inner join dbo.REVENUE
                      on INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID
                      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
                    where PAYMENTID = @ID

                    if @APPLICATIONCODE = 7
                      select
                        @APPLIEDRECIPRICOLCONSTITUENTNAME = CONSTITUENT.NAME,
                        @RECIPRICOLLOOKUPID = CONSTITUENT.LOOKUPID
                      from REVENUE
                        inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                        inner join dbo.REVENUEMATCHINGGIFT on REVENUE.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
                      where REVENUEMATCHINGGIFT.ID = @APPLIEDID
                  end
                  else
                    set @APPLIEDID = @REVENUEID;
                end

                if @APPLICATIONCODE = 6 -- Planned gift

                  select top 1
                    @APPLIEDID = REVENUE.ID,
                    @APPLIEDDATE = REVENUE.DATE,
                    @APPLIEDTYPE = REVENUE.TRANSACTIONTYPE,
                    @APPLIEDNAME = NF.NAME,
                    @ISPLANNEDGIFTADDITION = PLANNEDGIFTREVENUE.ISADDITION
                  from
                    dbo.INSTALLMENTSPLITPAYMENT
                  inner join
                    dbo.REVENUE on INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID
                  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
                  inner join
                    dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PLANNEDGIFTREVENUE on PLANNEDGIFTREVENUE.REVENUEID = REVENUE.ID
                  where
                    PAYMENTID = @ID;

                select top 1
                  @OPPORTUNITYID = RO.OPPORTUNITYID,
                  @OPPORTUNITYNAME = dbo.UFN_OPPORTUNITY_GETDESCRIPTION(RO.OPPORTUNITYID)
                from
                  dbo.REVENUEOPPORTUNITY RO
                where
                  RO.ID = @ID;

                if @APPLICATIONCODE = 12  --Auction item purchase

                    select top 1
                        @APPLIEDID = AUCTIONITEM.REVENUEAUCTIONDONATIONID,
                        @APPLIEDDATE = REVENUE.DATE,
                        @APPLIEDTYPE = REVENUE.TRANSACTIONTYPE,
                        @APPLIEDNAME = AUCTIONITEM.NAME,
                        @AUCTIONITEMPACKAGEID = AUCTIONITEM.PACKAGEID,
                        @AUCTIONITEMPACKAGENAME = (select NAME from dbo.AUCTIONITEM [ITEM] where [ITEM].ID = AUCTIONITEM.PACKAGEID)
                    from
                        dbo.AUCTIONITEM
                        inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
                        inner join dbo.REVENUESPLIT on AUCTIONITEMPURCHASE.PURCHASEID = REVENUESPLIT.ID
                        inner join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                    where
                        AUCTIONITEMPURCHASE.PURCHASEID = @ID

                --Gift Aid is for UK only

                if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
                begin
                  select @GIFTAIDQUALIFICATIONSTATUS = dbo.UFN_GIFTAIDREVENUESPLIT_GETQUALIFICATIONSTATUS(@ID);

                  -- @TAXCLAIMELIGIBILITY is deprecated.  Should use @TAXCLAIMELIGIBILITYSTATUSCODE instead.

                  select @TAXCLAIMELIGIBILITY = dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITY(@ID);
                  select @TAXCLAIMELIGIBILITYSTATUSCODE = dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITYSTATUS(@ID);

                  select @TAXCLAIMAMOUNT = dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT_2(@ID, 0, 1);
                  select @TAXCLAIMNUMBER = dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMNUMBER(@ID);
                  select @PREVIOUSCLAIMAMOUNT = dbo.UFN_GIFTAIDREVENUESPLIT_GETREFUNDTAXCLAIMAMOUNT(@ID);
                  select @PREVIOUSCLAIMNUMBER = dbo.UFN_GIFTAIDREVENUESPLIT_GETREFUNDTAXCLAIMNUMBER(@ID);

                  select @SHOWPOTENTIAL = dbo.UFN_GIFTAIDREVENUESPLIT_SHOWSPLITASPOTENTIAL(@ID);
                  select @SHOWREFUNDDETAILS = dbo.UFN_GIFTAIDREVENUESPLIT_SHOWREFUNDDETAILS(@ID);
                end

                if @APPLICATIONCODE = 13 --Donor challenge payment

                begin
                  select @APPLIEDTOCOUNT = count(*) 
                  from dbo.INSTALLMENTSPLITPAYMENT
                  inner join dbo.REVENUE
                    on INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID
                  inner join dbo.CONSTITUENT
                    on REVENUE.CONSTITUENTID = CONSTITUENT.ID
                  where PAYMENTID = @ID

                  if @APPLIEDTOCOUNT = 1 
                    select top 1
                      @APPLIEDID = REVENUE.ID,
                      @APPLIEDDATE  = REVENUE.DATE,
                      @APPLIEDTYPE = REVENUE.TRANSACTIONTYPE,
                      @APPLIEDNAME = NF.NAME
                    from dbo.INSTALLMENTSPLITPAYMENT
                    inner join dbo.REVENUE on INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID
                    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
                    where PAYMENTID = @ID
                  else
                    set @APPLIEDID = @REVENUEID;
                end

                if not @DONORCHALLENGEREVENUEID is null
                begin
                  select 
                    @DONORCHALLENGEAMOUNT = REVENUE.AMOUNT,
                    @DONORCHALLENGETRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
                    @DONORCHALLENGETYPE = REVENUE.TRANSACTIONTYPE,
                    @DONORCHALLENGECONSTITUENT = (select top 1 CONSTITUENT.NAME from dbo.CONSTITUENT where CONSTITUENT.ID = REVENUE.CONSTITUENTID)
                  from dbo.REVENUE
                  where REVENUE.ID = @DONORCHALLENGEREVENUEID
                end
                else
                begin
                  select 
                    @DONORCHALLENGEAMOUNT = 0,
                    @DONORCHALLENGETRANSACTIONCURRENCYID = null,
                    @DONORCHALLENGETYPE = N'',
                    @DONORCHALLENGECONSTITUENT = N''
                end

                return 0;