USP_DATALIST_OPPORTUNITYASSOCIATEDREVENUE

        CREATE procedure dbo.USP_DATALIST_OPPORTUNITYASSOCIATEDREVENUE
        (
          @OPPORTUNITYID uniqueidentifier
        ) as
          set nocount on;

          -- The RECORDASSOCIATION field is meant to tell pages where to go if they want to make a field on this list into a link.

          -- 0: Revenue

          -- 1: Event registration


          -- gifts, pledges, grant awards, planned gifts and matching gift claims

          select distinct
            FINANCIALTRANSACTION.ID,
            case
              when FINANCIALTRANSACTION.TYPECODE = 0 then REVENUESPLIT_EXT.APPLICATION
              else FINANCIALTRANSACTION.TYPE
            end as TYPE,
            cast(FINANCIALTRANSACTION.DATE as date) as DATE,
            sum(FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT) as AMOUNT,
            case 
              when FINANCIALTRANSACTION.TYPECODE in (1,3,4,6) then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(FINANCIALTRANSACTION.ID, isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID))
              else null
            end as BALANCE,
            NF.NAME,
            REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
            FINANCIALTRANSACTION.ID as TRANSACTIONID,
            null PARENTID,
            FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
            isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as BASECURRENCYID,
            0 as RECORDASSOCIATION
          from dbo.REVENUEOPPORTUNITY
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUEOPPORTUNITY.ID = FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTION.ID = V.FINANCIALTRANSACTIONID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) NF
          where
            FINANCIALTRANSACTION.DELETEDON is null and
            FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
            REVENUEOPPORTUNITY.OPPORTUNITYID = @OPPORTUNITYID and
            (
              FINANCIALTRANSACTION.TYPECODE in (1,3,4,6) or
              (
                FINANCIALTRANSACTION.TYPECODE = 0 and
                REVENUESPLIT_EXT.APPLICATIONCODE = 0
              )
            )
          group by
            FINANCIALTRANSACTION.ID, FINANCIALTRANSACTION.TYPECODE, REVENUESPLIT_EXT.APPLICATION,
            FINANCIALTRANSACTION.TYPE, FINANCIALTRANSACTION.DATE, NF.NAME,
            REVENUEPAYMENTMETHOD.PAYMENTMETHOD, FINANCIALTRANSACTION.TRANSACTIONCURRENCYID, REVENUE_EXT.NONPOSTABLEBASECURRENCYID,
            V.BASECURRENCYID

          union all

          -- event registrations

          select distinct
            REGISTRANT.ID,
            'Event registration' as TYPE,
            null as DATE,
            REGISTRANTREGISTRATION.AMOUNT,
            dbo.UFN_EVENTREGISTRANT_GETBALANCEINCURRENCY(REGISTRANT.ID, EVENT.BASECURRENCYID) as BALANCE,
            NF.NAME,
            'None' as PAYMENTMETHOD,
            REGISTRANT.ID as TRANSACTIONID,
            null as PARENTID,
            null as TRANSACTIONCURRENCYID,
            EVENT.BASECURRENCYID,
            1 as RECORDASSOCIATION
          from dbo.EVENTREGISTRATIONOPPORTUNITY
            inner join dbo.REGISTRANTREGISTRATION on EVENTREGISTRATIONOPPORTUNITY.ID = REGISTRANTREGISTRATION.REGISTRANTID
            inner join dbo.REGISTRANT on REGISTRANTREGISTRATION.REGISTRANTID = REGISTRANT.ID
            inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) NF
          where EVENTREGISTRATIONOPPORTUNITY.OPPORTUNITYID = @OPPORTUNITYID

          union all

          -- pledge payments

          select distinct
            INSTALLMENTREVENUESPLIT.ID,
            INSTALLMENTREVENUE.TYPE,
            cast(INSTALLMENTREVENUE.DATE as date) as DATE,
            INSTALLMENTREVENUESPLIT.BASEAMOUNT as AMOUNT,
            null as BALANCE,
            NF.NAME,
            (select REVENUEPAYMENTMETHOD.PAYMENTMETHOD from dbo.REVENUEPAYMENTMETHOD where REVENUEPAYMENTMETHOD.REVENUEID = INSTALLMENTREVENUE.ID) [PAYMENTMETHOD],
            INSTALLMENTREVENUE.ID as TRANSACTIONID,
            PLEDGEREVENUE.ID as PARENTID,
            INSTALLMENTREVENUE.TRANSACTIONCURRENCYID,
            isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as BASECURRENCYID,
            0 as RECORDASSOCIATION
          from dbo.INSTALLMENTSPLITPAYMENT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM INSTALLMENTREVENUESPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = INSTALLMENTREVENUESPLIT.ID
            inner join dbo.FINANCIALTRANSACTION INSTALLMENTREVENUE on INSTALLMENTREVENUESPLIT.FINANCIALTRANSACTIONID = INSTALLMENTREVENUE.ID
            inner join dbo.FINANCIALTRANSACTION PLEDGEREVENUE on INSTALLMENTSPLITPAYMENT.PLEDGEID = PLEDGEREVENUE.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM PLEDGEREVENUESPLIT on PLEDGEREVENUESPLIT.FINANCIALTRANSACTIONID = PLEDGEREVENUE.ID
            inner join dbo.REVENUEOPPORTUNITY on REVENUEOPPORTUNITY.ID = PLEDGEREVENUESPLIT.ID
            inner join dbo.REVENUE_EXT on INSTALLMENTREVENUE.ID = REVENUE_EXT.ID
            inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on INSTALLMENTREVENUESPLIT.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INSTALLMENTREVENUE.CONSTITUENTID) NF
          where
            PLEDGEREVENUE.DELETEDON is null and
            REVENUEOPPORTUNITY.OPPORTUNITYID = @OPPORTUNITYID

          union all

          -- event registration payments

          select distinct
            EVENTREGISTRANTPAYMENT.PAYMENTID,
            FINANCIALTRANSACTION.TYPE,
            cast(FINANCIALTRANSACTION.DATE as date) as DATE,
            FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
            null as BALANCE,
            NF.NAME,
            (select REVENUEPAYMENTMETHOD.PAYMENTMETHOD from dbo.REVENUEPAYMENTMETHOD where REVENUEPAYMENTMETHOD.REVENUEID = FINANCIALTRANSACTION.ID) [PAYMENTMETHOD],
            FINANCIALTRANSACTION.ID as TRANSACTIONID,
            EVENTREGISTRANTPAYMENT.REGISTRANTID as PARENTID,
            FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
            EVENT.BASECURRENCYID,
            0 as RECORDASSOCIATION
          from dbo.EVENTREGISTRANTPAYMENT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on EVENTREGISTRANTPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            inner join dbo.REGISTRANT on EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID
            inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
            inner join dbo.REVENUEOPPORTUNITY on FINANCIALTRANSACTIONLINEITEM.ID = REVENUEOPPORTUNITY.ID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) NF
          where
            FINANCIALTRANSACTION.DELETEDON is null and
            REVENUEOPPORTUNITY.OPPORTUNITYID = @OPPORTUNITYID;