USP_DATAFORMTEMPLATE_VIEW_REVENUETRANSACTIONDETAIL_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REVENUETRANSACTIONDETAIL_2
(
  @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,
  @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,
  @APPLIEDRECIPRICOLCONSTITUENTNAME nvarchar(400) = null output,
  @RECIPRICOLLOOKUPID nvarchar(100) = null output,
  @DONORCHALLENGECLAIMS xml = 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
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @LINKEDTOMEMBERSHIP = 0;
  set @DONATIONFORPLANNEDGIFT = 0;

  declare @REVENUEID uniqueidentifier;
  declare @CONSTITUENTID uniqueidentifier;
  declare @REVENUETYPECODE tinyint;
  declare @REVENUETYPE nvarchar(20);

  select
    @DATALOADED = 1,
    @REVENUEID = FINANCIALTRANSACTION.ID,
    @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
    @REVENUETYPECODE = REVENUESPLIT_EXT.TYPECODE,
    @REVENUETYPE = REVENUESPLIT_EXT.TYPE,
    @APPLICATIONCODE = REVENUESPLIT_EXT.APPLICATIONCODE,
    @DESIGNATION = DESIGNATION.NAME,
    @CATEGORYDESCRIPTION = coalesce(GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME, ''),
    @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    @SOLICITORS = dbo.UFN_REVENUE_GETSOLICITORS_2_TOITEMLISTXML(@ID),
    @RECOGNITIONS = dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS_TOITEMLISTXML(@ID)
  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 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 top 1
    @OPPORTUNITYID = RO.OPPORTUNITYID,
    @OPPORTUNITYNAME = dbo.UFN_OPPORTUNITY_GETDESCRIPTION(RO.OPPORTUNITYID)
  from
    dbo.REVENUEOPPORTUNITY RO
  where
    RO.ID = @ID;

  select
    @CONSTITUENTISGROUP = (ISGROUP|ISORGANIZATION)
  from
    dbo.CONSTITUENT
  where
    ID = @CONSTITUENTID;

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

  select @DONORCHALLENGECLAIMS = dbo.UFN_REVENUETRANSACTION_TOP3DONORCHALLENGECLAIMS_TOITEMLISTXML(@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);
    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 = 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 = 1 --Event Fee payment
  begin
    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;
  end

  if @APPLICATIONCODE in (2, 8, 19) --pledge payment, grant award, membership installment plan
  begin
    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
      begin
        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
          INSTALLMENTSPLITPAYMENT.PAYMENTID = @ID and
          FINANCIALTRANSACTION.DELETEDON is null and
          FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
          FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and
          MEMBER.ISPRIMARY = 1;
      end
      else
      begin
        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
          INSTALLMENTSPLITPAYMENT.PAYMENTID = @ID and
          FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
          FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;
      end
    end
  end

  if @APPLICATIONCODE = 3 --recurring gift payment
  begin
    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 dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID)
                from dbo.SPONSORSHIPRECURRINGADDITIONALGIFT SPRAG
                inner join dbo.SPONSORSHIP on SPONSORSHIP.ID = SPRAG.SPONSORSHIPID
                where SPRAG.REVENUEID = FINANCIALTRANSACTION.ID
              ) + ')'
          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 = 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 = 6 -- Planned gift
  begin
    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;
  end

  if @APPLICATIONCODE in (7, 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
      begin
        select
          @APPLIEDRECIPRICOLCONSTITUENTNAME = CONSTITUENT.NAME,
          @RECIPRICOLLOOKUPID = CONSTITUENT.LOOKUPID
        from
          dbo.REVENUE
        inner join
          dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
        inner join
          dbo.REVENUEMATCHINGGIFT on REVENUE.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
        where
          REVENUEMATCHINGGIFT.ID = @APPLIEDID;
      end
    end
    else
    begin
      set @APPLIEDID = @REVENUEID;
    end
  end

  if @APPLICATIONCODE = 12  --Auction item purchase
  begin
    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;
  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
    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
      inner join
        dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
      outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
      where
        PAYMENTID = @ID;
    end
    else
    begin
      set @APPLIEDID = @REVENUEID;
    end
  end

  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

  return 0;