USP_DATALIST_CONSTITUENTMEMBERSHIPINFOFORDUES

CREATE procedure dbo.USP_DATALIST_CONSTITUENTMEMBERSHIPINFOFORDUES
(
    @CONSTITUENTID uniqueidentifier,
    @PDACCOUNTSYSTEMID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier = null
)
as
    set nocount on;

    declare @ISGROUP bit = 0;

    select
        @ISGROUP = ISGROUP
    from dbo.CONSTITUENT 
    where
        ID = @CONSTITUENTID;

    declare @CURRENCYSETID uniqueidentifier
    select @CURRENCYSETID = CURRENCYSETID
    from dbo.PDACCOUNTSYSTEM
    where ID = @PDACCOUNTSYSTEMID

    declare @BASECURRENCYID uniqueidentifier
    select
        @BASECURRENCYID = CURRENCY.ID
    from
        dbo.CURRENCYSET
        inner join dbo.CURRENCY on CURRENCY.ID = CURRENCYSET.BASECURRENCYID
    where
        CURRENCYSET.ID = coalesce(@CURRENCYSETID,dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET())

    declare @CONSTITUENTS table (ID uniqueidentifier);

    insert into @CONSTITUENTS
    select @CONSTITUENTID as ID
    union all
    select ID from dbo.UFN_GROUP_GETCURRENTMEMBERS(@CONSTITUENTID, @CURRENTAPPUSERID, '3550edaf-780c-48c8-9700-f478eeac9e51', 0)
    union all
    select ID from dbo.UFN_CONSTITUENT_GETGROUPS(@CONSTITUENTID) where dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1;

    declare @RESULTSTABLE table
    (
        MEMBERSHIPPROGRAMID uniqueidentifier,
        MEMBERSHIPLEVELID uniqueidentifier,
        MEMBERSHIPLEVELTERMID uniqueidentifier,
        ISRENEWAL bit,
        MEMBERSHIPNAME nvarchar(100),
        EXPIRATIONDATE datetime,
        HASCONTRIBUTORYPART bit,
        MEMBERSHIPLEVELDESIGNATIONS xml,
        MEMBERSHIPLEVELBENEFITS xml,
        TAXDEDUCTIBLEAMOUNT money,
        CANBEPAIDINFULL bit,
        CANBEPLEDGED bit,
        MEMBERSHIPID uniqueidentifier,
        PLEDGEID uniqueidentifier,
        PLEDGECURRENCY uniqueidentifier,
        PLEDGEBALANCE money,
        NEXTINSTALLMENTAMOUNT money,
        NEXTINSTALLMENTDATE datetime,
        EXPIRESONCODE tinyint,
        CUTOFFDAY tinyint,
        CUTOFFDAYFORYEAR UDT_MONTHDAY,
        BASECURRENCYID uniqueidentifier,
        PROGRAMTYPECODE tinyint,
        ISRECURRINGGIFT bit,
        RECURRINGGIFTCURRENCY uniqueidentifier,
        NEXTRECURRINGGIFTINSTALLMENTDATE datetime,
        NEXTRECURRINGGIFTAMOUNT money,
        CONSTITUENTNAME nvarchar(300),
        CONSTITUENTID uniqueidentifier,
        DEDUCTIBILITYCODE tinyint,
        RENEWALWINDOWEND tinyint,
        RENEWALWINDOWREVENUETYPECODE tinyint,
        NUMBEROFCHILDREN smallint,
        RECURRINGGIFTISACTIVE bit,
        INSTALLMENTS xml,
        AUTOMATICALLYRENEWMEMBERSHIP bit,
        CONTRIBUTIONBASEDEXPRIESONCODE tinyint,
        ISCANCELLED bit,
        PLEDGEPDACCOUNTSYSTEMID uniqueidentifier,
        ISPENDING bit,
        RECURRINGGIFTREMAININGBALANCE money,
        HASTYPES bit,
        CURRENTMEMBERSHIPTYPE uniqueidentifier,
        ISUPGRADEABLE bit,
        RENEWALSTARTDATE datetime,
        CHILDRENALLOWED smallint,
        MEMBERSALLOWED smallint,
        MEMBERSHIPLEVELNAME nvarchar(100),
        CANCELDATE datetime,
        INSTALLMENTID uniqueidentifier,
        RECURRINGGIFTID uniqueidentifier,
        RECURRINGGIFTINSTALLMENTID uniqueidentifier,
        COMMITMENTISPENDING bit,
        MEMBERSHIPLEVELTERMNAME nvarchar(100),
        NUMBEROFCARDSALLOWED smallint
    )

    insert into @RESULTSTABLE
    (
        MEMBERSHIPPROGRAMID,
        MEMBERSHIPLEVELID,
        MEMBERSHIPLEVELTERMID,
        ISRENEWAL,
        MEMBERSHIPNAME,
        EXPIRATIONDATE,
        HASCONTRIBUTORYPART,
        MEMBERSHIPLEVELDESIGNATIONS,
        MEMBERSHIPLEVELBENEFITS,
        TAXDEDUCTIBLEAMOUNT,
        CANBEPAIDINFULL,
        CANBEPLEDGED,
        MEMBERSHIPID,
        EXPIRESONCODE,
        CUTOFFDAY,
        CUTOFFDAYFORYEAR,
        BASECURRENCYID,
        PROGRAMTYPECODE,
        CONSTITUENTNAME,
        CONSTITUENTID,
        DEDUCTIBILITYCODE,
        RENEWALWINDOWEND,
        RENEWALWINDOWREVENUETYPECODE,
        NUMBEROFCHILDREN,
        AUTOMATICALLYRENEWMEMBERSHIP,
        CONTRIBUTIONBASEDEXPRIESONCODE,
        ISCANCELLED,
        ISRECURRINGGIFT,
        RECURRINGGIFTISACTIVE,
        ISPENDING,
        HASTYPES,
        CURRENTMEMBERSHIPTYPE,
        ISUPGRADEABLE,
        RENEWALSTARTDATE,
        CHILDRENALLOWED,
        MEMBERSALLOWED,
        MEMBERSHIPLEVELNAME,
        CANCELDATE,
        PLEDGEID,
        RECURRINGGIFTID,
        COMMITMENTISPENDING,
        MEMBERSHIPLEVELTERMNAME,
        NUMBEROFCARDSALLOWED
    )
    select
        MEMBERSHIP.MEMBERSHIPPROGRAMID,
        MEMBERSHIP.MEMBERSHIPLEVELID,
        MEMBERSHIP.MEMBERSHIPLEVELTERMID,
        case MEMBERSHIP.STATUSCODE
            when 1 then 1
            when 5 then 1
            when 2 then 1
            else
                case MEMBERSHIPPROGRAM.PROGRAMTYPECODE
                    when 0 then
                        case
                            when dbo.UFN_MEMBERSHIP_HASOUTSTANDINGPLEDGE(MEMBERSHIP.ID) = 1 then 0
                            else 1
                        end
                else 0                                
                end
        end as ISRENEWAL,
        MEMBERSHIPPROGRAM.NAME as MEMBERSHIPNAME,
        case
            when MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 0 then
                case when MEMBERSHIP.STATUSCODE = 2 then getdate() else MEMBERSHIP.EXPIRATIONDATE end
            else null
        end as EXPIRATIONDATE,
        case when MEMBERSHIPPROGRAM.DEDUCTIBILITYCODE <> 2 and MEMBERSHIPPROGRAM.DUESTREATEDASCONTRIBUTION = 1 then 1 else 0 end as HASCONTRIBUTORYPART,
        (select DESIGNATION.ID, DESIGNATION.NAME, MEMBERSHIPLEVELDESIGNATION.[PERCENT]
            from dbo.MEMBERSHIPLEVELDESIGNATION
            inner join dbo.DESIGNATION on MEMBERSHIPLEVELDESIGNATION.DESIGNATIONID = DESIGNATION.ID
            where MEMBERSHIPLEVELDESIGNATION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
            for xml raw('ITEM'),type,elements,root('MEMBERSHIPLEVELDESGINATIONS'),BINARY BASE64
        ) as MEMBERSHIPLEVELDESIGNATIONS,
        (select
            MEMBERSHIPLEVELBENEFIT.BENEFITID
            , BENEFIT.NAME
            , MEMBERSHIPLEVELBENEFIT.UNITVALUE
            , MEMBERSHIPLEVELBENEFIT.BASECURRENCYID
            , MEMBERSHIPLEVELBENEFIT.QUANTITY
            , MEMBERSHIPLEVELBENEFIT.USEPERCENT
            , MEMBERSHIPLEVELBENEFIT.VALUEPERCENT
            , MEMBERSHIPLEVELBENEFIT.FREQUENCYCODE
            , MEMBERSHIPLEVELBENEFIT.DETAILS
            from dbo.MEMBERSHIPLEVELBENEFIT
            inner join dbo.BENEFIT on MEMBERSHIPLEVELBENEFIT.BENEFITID = BENEFIT.ID
            where MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
            order by MEMBERSHIPLEVELBENEFIT.SEQUENCE
            for xml raw('ITEM'),type,elements,root('MEMBERSHIPLEVELBENEFITS'),BINARY BASE64
        ) as MEMBERSHIPLEVELBENEFITS,
        coalesce(
            case
                -- Entire amount

                when MEMBERSHIPPROGRAM.DEDUCTIBILITYCODE = 0 then
                    case
                        when MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 0 then MEMBERSHIPLEVEL.RECEIPTAMOUNT
                        else MEMBERSHIPLEVELTERM.AMOUNT
                    end
                -- Portion

                when MEMBERSHIPPROGRAM.DEDUCTIBILITYCODE = 1 then MEMBERSHIPLEVEL.RECEIPTAMOUNT
                -- None

                else 0
            end, 0) as TAXDEDUCTIBLEAMOUNT,
        MEMBERSHIPPROGRAM.ONEPAYMENTEACHTERM as CANBEPAIDINFULL,
        MEMBERSHIPPROGRAM.MULTIPLEPAYMENTSEACHTERM as CANBEPLEDGED,
        MEMBERSHIP.ID as MEMBERSHIPID,
        MEMBERSHIPPROGRAM.EXPIRESONCODE,
        MEMBERSHIPPROGRAM.CUTOFFDAY,
        MEMBERSHIPPROGRAM.CUTOFFDATEFORYEAR,
        MEMBERSHIPPROGRAM.BASECURRENCYID,
        MEMBERSHIPPROGRAM.PROGRAMTYPECODE,
        NF.NAME as CONSTITUENTNAME,
        MEMBER.CONSTITUENTID as CONSTITUENTID,
        MEMBERSHIPPROGRAM.DEDUCTIBILITYCODE,
        MEMBERSHIPLEVEL.AFTEREXPIRATION as RENEWALWINDOWEND,
        MEMBERSHIPPROGRAM.RENEWALWINDOWREVENUETYPECODE as RENEWALWINDOWREVENUETYPECODE,
        MEMBERSHIP.NUMBEROFCHILDREN, -- Temporary workaround for children not implemented in 2012 Q1

        coalesce(MEMBERSHIP.AUTOMATICALLYRENEWMEMBERSHIP,0),
        coalesce(MEMBERSHIPPROGRAMCONTRIBUTION.WHATDATETOCALCULATEEXPIRATIONDATECODE, 0) as CONTRIBUTIONBASEDEXPIRESONCODE,
        case MEMBERSHIP.STATUSCODE when 1 then 1 else 0 end as ISCANCELLED,
        0 as ISRECURRINGGIFT,
        0 as RECURRINGGIFTISACTIVE,
        case MEMBERSHIP.STATUSCODE when 2 then 1 else 0 end as ISPENDING,
        case when exists (select top 1 MEMBERSHIPLEVELTYPE.ID from dbo.MEMBERSHIPLEVELTYPE 
                                        inner join dbo.MEMBERSHIPLEVEL ML on ML.ID = MEMBERSHIPLEVEL.ID)
                    then 1
                    else 0
        end as HASTYPES,
        MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID as CURRENTMEMBERSHIPTYPE,
        MEMBERSHIPSTATE.UPGRADEABLESTATUS as ISUPGRADEABLE,
        dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALBEFOREEXPIRATIONDATE(MEMBERSHIPLEVEL.ID, dbo.MEMBERSHIP.EXPIRATIONDATE) as RENEWALSTARTDATE,
        MEMBERSHIPLEVEL.CHILDRENALLOWED,
        MEMBERSHIPLEVEL.MEMBERSALLOWED,
        MEMBERSHIPLEVEL.NAME as MEMBERSHIPLEVELNAME,
        null as CANCELDATE,
        dbo.UFN_MEMBERSHIP_GETPLEDGE(MEMBERSHIP.ID) as PLEDGEID,
        dbo.UFN_MEMBERSHIP_GETRECURRINGGIFT(MEMBERSHIP.ID) as RECURRINGGIFTID,
        0, --COMMITMENTISPENDING

                            case MEMBERSHIPPROGRAM.PROGRAMTYPECODE when 0 then cast(MEMBERSHIPLEVELTERM.TERMTIMELENGTH as nvarchar(5)) + ' ' + MEMBERSHIPLEVELTERM.TERMLENGTH
                                                                                                         when 1 then MEMBERSHIPLEVELTERM.RECURRINGPAYMENTOPTION
                                                                                                         else case when MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTIONCODE = 0 then MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTION 
                                                                                                                             else cast(MEMBERSHIPLEVELTERM.LIFETIMENUMBEROFPAYMENTS as nvarchar(5)) + ' ' + MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTION + ' payments' end end as MEMBERSHIPLEVELTERMNAME,
        MEMBERSHIPLEVEL.CARDSALLOWED
    from dbo.MEMBER
        inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
        inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
        inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
        inner join @CONSTITUENTS C on C.ID = MEMBER.CONSTITUENTID
        left join dbo.SITE on MEMBERSHIPPROGRAM.SITEID = SITE.ID
        left outer join dbo.MEMBERSHIPPROGRAMCONTRIBUTION on MEMBERSHIPPROGRAMCONTRIBUTION.ID = MEMBERSHIPPROGRAM.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID) NF
        cross apply (
          SELECT
                case MEMBERSHIP.STATUSCODE
                    when 0 then
                        case
                            when MEMBERSHIPPROGRAM.PROGRAMTYPECODE IN (0,2) then
                                case
                                    when dbo.UFN_MEMBERSHIP_HASOUTSTANDINGPLEDGE(MEMBERSHIP.ID) = 1 then 0
                                    else 1
                                end
                            else 0
                        end
                    when 2 then 1
                    else 0
                end as UPGRADEABLESTATUS
        ) as MEMBERSHIPSTATE
    where
    (-- Paying for existing membership or program is active

        MEMBERSHIP.STATUSCODE = 2 
        or MEMBERSHIPPROGRAM.PROGRAMTYPECODE <> 0
        or dbo.UFN_MEMBERSHIP_HASOUTSTANDINGPLEDGE(MEMBERSHIP.ID) = 1
        or MEMBERSHIPPROGRAM.ISACTIVE = 1
    ) 
    and MEMBER.ISDROPPED = 0
    and
    (
        MEMBERSHIPPROGRAM.PROGRAMTYPECODE <> 2 or
        (
            MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 2 and
            (
                ( --cancelled with non-zero balance, for rejoin

                  MEMBERSHIP.STATUSCODE = 1 or
                  dbo.UFN_PLEDGE_GETBALANCE(dbo.UFN_MEMBERSHIP_GETPLEDGE(MEMBERSHIP.ID)) > 0
                ) or
                ( --active with zero balance, for upgrade

                  MEMBERSHIP.STATUSCODE = 0 or
                  dbo.UFN_PLEDGE_GETBALANCE(dbo.UFN_MEMBERSHIP_GETPLEDGE(MEMBERSHIP.ID)) = 0
                )
            )
        )
    )
    and
    (
        MEMBERSHIPPROGRAM.PROGRAMTYPECODE <> 1 or
            (
                MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 1 and MEMBERSHIPPROGRAM.WHEREISREVENUETRACKEDCODE = 0
            ) or
            (
                MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 1 and MEMBERSHIPPROGRAM.WHEREISREVENUETRACKEDCODE = 1 and MEMBERSHIP.STATUSCODE = 1
            )
    )

    update @RESULTSTABLE set
        INSTALLMENTID = dbo.UFN_PLEDGE_GETNEXTINSTALLMENT(PLEDGEID, null),
        RECURRINGGIFTINSTALLMENTID = dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENT(RECURRINGGIFTID, null)
    where
        PLEDGEID is not null
        or RECURRINGGIFTID is not null;

    update @RESULTSTABLE set
        PLEDGEID = FINANCIALTRANSACTION.ID,
        PLEDGECURRENCY = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
        PLEDGEBALANCE = dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID),
        NEXTINSTALLMENTAMOUNT = dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID),
        NEXTINSTALLMENTDATE = INSTALLMENT.DATE,
        PLEDGEPDACCOUNTSYSTEMID = FINANCIALTRANSACTION.PDACCOUNTSYSTEMID,
        COMMITMENTISPENDING = REVENUESCHEDULE.ISPENDING,
        INSTALLMENTS =
            (
                select
                    SEQUENCE,
                    INSTALLMENT.AMOUNT,
                    isnull(SPLITAMOUNTS.DONATION, 0) as CONTRIBUTEDAMOUNT,
                    isnull((
                        select sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
                        from dbo.INSTALLMENTSPLITPAYMENT
                            inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        where
                            INSTALLMENTSPLITPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID and
                            INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                    ), 0) as PAID,
                    isnull((
                        select sum(INSTALLMENTSPLITWRITEOFF.AMOUNT)
                        from dbo.INSTALLMENTSPLIT
                            inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
                        where 
                            INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                    ), 0) as WRITTENOFF,
                    isnull(WRITTENOFFAMOUNTS.DONATION, 0) as CONTRIBUTEDWRITTENOFF,
                    isnull(PAIDAMOUNTS.DONATION, 0) as CONTRIBUTEDPAID,
                    isnull(SPLITAMOUNTS.MEMBERSHIP, 0) as EARNEDAMOUNT,
                    isnull(WRITTENOFFAMOUNTS.MEMBERSHIP, 0) as EARNEDWRITTENOFF,
                    isnull(PAIDAMOUNTS.MEMBERSHIP, 0) as EARNEDPAID,
                    isnull(SPLITAMOUNTS.ADDON, 0) as ADDONAMOUNT,
                    isnull(WRITTENOFFAMOUNTS.ADDON, 0) as ADDONWRITTENOFF,
                    isnull(PAIDAMOUNTS.ADDON, 0) as ADDONPAID
                from dbo.INSTALLMENT
                outer apply (
                    select 
                        sum(AMOUNTS.DONATION) as DONATION,
                        sum(AMOUNTS.MEMBERSHIP) as MEMBERSHIP,
                        sum(AMOUNTS.ADDON) as ADDON
                    from dbo.INSTALLMENTSPLIT
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    cross apply(
                        select 
                            case when REVENUESPLIT_EXT.TYPECODE = 0 then INSTALLMENTSPLIT.AMOUNT else 0 end as DONATION,
                            case when REVENUESPLIT_EXT.APPLICATIONCODE = 5 then INSTALLMENTSPLIT.AMOUNT else 0 end as MEMBERSHIP,
                            case when REVENUESPLIT_EXT.APPLICATIONCODE = 18 then INSTALLMENTSPLIT.AMOUNT else 0 end as ADDON
                    ) as AMOUNTS
                    where INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                ) SPLITAMOUNTS
                outer apply (
                    select 
                        sum(AMOUNTS.DONATION) as DONATION,
                        sum(AMOUNTS.MEMBERSHIP) as MEMBERSHIP,
                        sum(AMOUNTS.ADDON) as ADDON
                    from dbo.INSTALLMENTSPLIT
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
                    cross apply(
                        select 
                            case when REVENUESPLIT_EXT.TYPECODE = 0 then INSTALLMENTSPLITPAYMENT.AMOUNT else 0 end as DONATION,
                            case when REVENUESPLIT_EXT.APPLICATIONCODE = 5 then INSTALLMENTSPLITPAYMENT.AMOUNT else 0 end as MEMBERSHIP,
                            case when REVENUESPLIT_EXT.APPLICATIONCODE = 18 then INSTALLMENTSPLITPAYMENT.AMOUNT else 0 end as ADDON
                    ) as AMOUNTS
                    where 
                        INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID and
                        INSTALLMENTSPLITPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID
                ) PAIDAMOUNTS
                outer apply (
                    select 
                        sum(AMOUNTS.DONATION) as DONATION,
                        sum(AMOUNTS.MEMBERSHIP) as MEMBERSHIP,
                        sum(AMOUNTS.ADDON) as ADDON
                    from dbo.INSTALLMENTSPLIT
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
                    cross apply(
                        select 
                            case when REVENUESPLIT_EXT.TYPECODE = 0 then INSTALLMENTSPLITWRITEOFF.AMOUNT else 0 end as DONATION,
                            case when REVENUESPLIT_EXT.APPLICATIONCODE = 5 then INSTALLMENTSPLITWRITEOFF.AMOUNT else 0 end as MEMBERSHIP,
                            case when REVENUESPLIT_EXT.APPLICATIONCODE = 18 then INSTALLMENTSPLITWRITEOFF.AMOUNT else 0 end as ADDON
                    ) as AMOUNTS
                    where INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                ) WRITTENOFFAMOUNTS
                where
                    REVENUEID = FINANCIALTRANSACTION.ID
                    order by INSTALLMENT.SEQUENCE asc
                for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64
            )
    from @RESULTSTABLE MEMBERSHIPS
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = MEMBERSHIPS.PLEDGEID
        inner join dbo.INSTALLMENT on INSTALLMENT.ID = MEMBERSHIPS.INSTALLMENTID
        inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID

    update @RESULTSTABLE set
        ISRECURRINGGIFT = 1,
        RECURRINGGIFTCURRENCY = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
        NEXTRECURRINGGIFTINSTALLMENTDATE = RECURRINGGIFTINSTALLMENT.DATE,
        NEXTRECURRINGGIFTAMOUNT = dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(RECURRINGGIFTINSTALLMENT.ID),
        RECURRINGGIFTISACTIVE = case when dbo.UFN_RECURRINGGIFT_GETSTATUSCODE(FINANCIALTRANSACTION.ID) = 0 then 1 else 0 end,
        RECURRINGGIFTREMAININGBALANCE = dbo.UFN_RECURRINGGIFT_GETREMAININGBALANCE(FINANCIALTRANSACTION.ID),
        COMMITMENTISPENDING = REVENUESCHEDULE.ISPENDING
    from @RESULTSTABLE MEMBERSHIPS
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = MEMBERSHIPS.RECURRINGGIFTID
        inner join dbo.RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.ID = MEMBERSHIPS.RECURRINGGIFTINSTALLMENTID
        inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID

    update @RESULTSTABLE set 
    CANCELDATE = (select top 1 MEMBERSHIPTRANSACTION.TRANSACTIONDATE 
                                from dbo.MEMBERSHIPTRANSACTION
                                where MEMBERSHIPTRANSACTION.MEMBERSHIPID = RESULTSTABLE.MEMBERSHIPID and 
                                RESULTSTABLE.ISCANCELLED = 1 and 
                                MEMBERSHIPTRANSACTION.ACTIONCODE = 4
                                order by MEMBERSHIPTRANSACTION.TRANSACTIONDATE desc
                                )
    from @RESULTSTABLE RESULTSTABLE

    delete from @RESULTSTABLE
    where ((PLEDGEID is null and BASECURRENCYID <> @BASECURRENCYID ) or (PLEDGEID is not null and @PDACCOUNTSYSTEMID <> PLEDGEPDACCOUNTSYSTEMID))

    select
        MEMBERSHIPPROGRAMID,
        MEMBERSHIPLEVELID,
        MEMBERSHIPLEVELTERMID,
        ISRENEWAL,
        MEMBERSHIPNAME,
        EXPIRATIONDATE,
        HASCONTRIBUTORYPART,
        MEMBERSHIPLEVELDESIGNATIONS,
        MEMBERSHIPLEVELBENEFITS,
        TAXDEDUCTIBLEAMOUNT,
        CANBEPAIDINFULL,
        CANBEPLEDGED,
        MEMBERSHIPID,
        PLEDGEID,
        PLEDGECURRENCY,
        PLEDGEBALANCE,
        NEXTINSTALLMENTAMOUNT,
        NEXTINSTALLMENTDATE,
        EXPIRESONCODE,
        CUTOFFDAY,
        CUTOFFDAYFORYEAR,
        BASECURRENCYID,
        PROGRAMTYPECODE,
        ISRECURRINGGIFT,
        RECURRINGGIFTCURRENCY,
        NEXTRECURRINGGIFTINSTALLMENTDATE,
        NEXTRECURRINGGIFTAMOUNT,
        CONSTITUENTNAME,
        CONSTITUENTID,
        DEDUCTIBILITYCODE,
        RENEWALWINDOWEND,
        RENEWALWINDOWREVENUETYPECODE,
        NUMBEROFCHILDREN,
        RECURRINGGIFTISACTIVE,
        INSTALLMENTS,
        AUTOMATICALLYRENEWMEMBERSHIP,
        CONTRIBUTIONBASEDEXPRIESONCODE,
        ISCANCELLED,
        ISPENDING,
        RECURRINGGIFTREMAININGBALANCE,
        HASTYPES,
        CURRENTMEMBERSHIPTYPE,
        ISUPGRADEABLE,
        RENEWALSTARTDATE,
        CHILDRENALLOWED,
        MEMBERSALLOWED,
        MEMBERSHIPLEVELNAME,
        CANCELDATE,
        COMMITMENTISPENDING,
        MEMBERSHIPLEVELTERMNAME,
        NUMBEROFCARDSALLOWED
    from @RESULTSTABLE

    return 0;