USP_MEMBERSHIPPROGRAMCONTRIBUTION_MEMBERSHIPREVENUETOCONSIDER_POPULATE

create procedure dbo.USP_MEMBERSHIPPROGRAMCONTRIBUTION_MEMBERSHIPREVENUETOCONSIDER_POPULATE (
    @MEMBERSHIPPROGRAMID uniqueidentifier,
    @LASTRUNONDATE datetime,
    @PROCESSDATE datetime,
    @MINIMUMDATERANGE datetime
)
as
begin
    insert into #CONTRIBUTIONPROCESS_MEMBERSHIPREVENUETOCONSIDER (FINANCIALTRANSACTIONLINEITEMID)
    select distinct MEMBERSHIPTRANSACTION.REVENUESPLITID 
    from dbo.MEMBERSHIPPROGRAM
    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
    inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
    left join dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE on 
        MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
        and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
    where 
        FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
        and MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE.FINANCIALTRANSACTIONLINEITEMID is null

    update #CONTRIBUTIONPROCESS_MEMBERSHIPREVENUETOCONSIDER 
    set 
        FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID,
        CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
        AMOUNTINCURRENCY = FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT - dbo.UFN_MEMBERSHIP_GETBASEAMOUNTDISCOUNT(MEMBERSHIPTRANSACTION.ID),
        REVENUETYPECODE = FINANCIALTRANSACTION.TYPECODE,
        EFFECTIVEDATE = FINANCIALTRANSACTION.DATE,
        APPLICATIONCODE = REVENUESPLIT_EXT.APPLICATIONCODE,
        SPLITTYPECODE = REVENUESPLIT_EXT.TYPECODE
    from #CONTRIBUTIONPROCESS_MEMBERSHIPREVENUETOCONSIDER SPLITSTOCONSIDER 
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = SPLITSTOCONSIDER.FINANCIALTRANSACTIONLINEITEMID 
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = SPLITSTOCONSIDER.FINANCIALTRANSACTIONLINEITEMID 
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
    inner join dbo.MEMBERSHIPTRANSACTION on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID

    insert into #CONTRIBUTIONPROCESS_MEMBERSHIPREVENUETOCONSIDER
    (
        FINANCIALTRANSACTIONID,
        FINANCIALTRANSACTIONLINEITEMID,
        CONSTITUENTID,
        AMOUNTINCURRENCY,
        EFFECTIVEDATE,
        REVENUETYPECODE
    )
    select
        MEMBERSHIPREVENUETOCONSIDER.FINANCIALTRANSACTIONID,
        FINANCIALTRANSACTIONLINEITEM.ID,
        MEMBERSHIPREVENUETOCONSIDER.CONSTITUENTID,
        FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
        MEMBERSHIPREVENUETOCONSIDER.EFFECTIVEDATE,
        0
    from #CONTRIBUTIONPROCESS_MEMBERSHIPREVENUETOCONSIDER MEMBERSHIPREVENUETOCONSIDER 
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = MEMBERSHIPREVENUETOCONSIDER.FINANCIALTRANSACTIONID
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
    where 
        (
            MEMBERSHIPREVENUETOCONSIDER.REVENUETYPECODE = 0
            and REVENUESPLIT_EXT.TYPECODE = 0
            and REVENUESPLIT_EXT.APPLICATIONCODE = 0 
            and exists (
                select 1 
                from dbo.MEMBERSHIPCONTRIBUTIONPORTION 
                where FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
            )
        )
        or 
        (
            REVENUESPLIT_EXT.APPLICATIONCODE = 18
            and (MEMBERSHIPREVENUETOCONSIDER.EFFECTIVEDATE >= @LASTRUNONDATE or @LASTRUNONDATE is null) 
            and (MEMBERSHIPREVENUETOCONSIDER.EFFECTIVEDATE between @MINIMUMDATERANGE and @PROCESSDATE)
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
        )

    insert into #CONTRIBUTIONPROCESS_MEMBERSHIPREVENUETOCONSIDER
    (
        FINANCIALTRANSACTIONID,
        FINANCIALTRANSACTIONLINEITEMID,
        CONSTITUENTID,
        AMOUNTINCURRENCY,
        EFFECTIVEDATE,
        REVENUETYPECODE
    )
    select
        RECURRINGGIFTACTIVITY.SOURCEREVENUEID,
        RECURRINGGIFTACTIVITY.PAYMENTREVENUEID,
        MEMBERSHIPREVENUETOCONSIDER.CONSTITUENTID,
        RECURRINGGIFTACTIVITY.AMOUNT,
        FINANCIALTRANSACTION.DATE,
        0
    from #CONTRIBUTIONPROCESS_MEMBERSHIPREVENUETOCONSIDER MEMBERSHIPREVENUETOCONSIDER 
    inner join dbo.RECURRINGGIFTACTIVITY on RECURRINGGIFTACTIVITY.SOURCEREVENUEID = MEMBERSHIPREVENUETOCONSIDER.FINANCIALTRANSACTIONID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
    where RECURRINGGIFTACTIVITY.PAYMENTREVENUEID <> MEMBERSHIPREVENUETOCONSIDER.FINANCIALTRANSACTIONLINEITEMID
        and (FINANCIALTRANSACTION.DATE >= @LASTRUNONDATE or @LASTRUNONDATE is null) 
        and (FINANCIALTRANSACTION.DATE between @MINIMUMDATERANGE and @PROCESSDATE)
        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null

    insert into #CONTRIBUTIONPROCESS_MEMBERSHIPREVENUETOCONSIDER
    (
        FINANCIALTRANSACTIONID,
        FINANCIALTRANSACTIONLINEITEMID,
        CONSTITUENTID,
        AMOUNTINCURRENCY,
        EFFECTIVEDATE,
        REVENUETYPECODE
    )
    select
        INSTALLMENTPAYMENT.PLEDGEID,
        INSTALLMENTPAYMENT.PAYMENTID,
        MEMBERSHIPREVENUETOCONSIDER.CONSTITUENTID,
        INSTALLMENTPAYMENT.AMOUNT,
        FINANCIALTRANSACTION.DATE,
        0
    from #CONTRIBUTIONPROCESS_MEMBERSHIPREVENUETOCONSIDER MEMBERSHIPREVENUETOCONSIDER 
    inner join dbo.INSTALLMENTPAYMENT on INSTALLMENTPAYMENT.PLEDGEID = MEMBERSHIPREVENUETOCONSIDER.FINANCIALTRANSACTIONID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTPAYMENT.PAYMENTID
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
    where 
        INSTALLMENTPAYMENT.PAYMENTID <> MEMBERSHIPREVENUETOCONSIDER.FINANCIALTRANSACTIONLINEITEMID
        and (FINANCIALTRANSACTION.DATE >= @LASTRUNONDATE or @LASTRUNONDATE is null)
        and (FINANCIALTRANSACTION.DATE between @MINIMUMDATERANGE and @PROCESSDATE)
        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
end