USP_DATALIST_REVENUEUPDATEBATCHCONSTITUENTUNREALIZEDREVENUE

CREATE procedure dbo.USP_DATALIST_REVENUEUPDATEBATCHCONSTITUENTUNREALIZEDREVENUE
(
    @REVENUEID uniqueidentifier,
    @CONSTITUENTID uniqueidentifier,
    @LOADALL bit = 0,  
    @CURRENTAPPUSERID uniqueidentifier = null,  
    @SECURITYFEATUREID uniqueidentifier = null,  
    @SECURITYFEATURETYPE tinyint = null 
)
as
    set nocount on;

    declare @PAYMENTAPPLIEDTABLE table
    (
        REVENUEID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        CONSTITUENTNAME nvarchar(255),
        APPLIED money,
        BALANCE money,
        AMOUNTDUE money,
        DATEDUE datetime,
        REVENUETYPECODE tinyint,
        DESCRIPTION nvarchar(255),
        SORTORDER int,
        DESIGNATIONLIST nvarchar(500),
        SPONSORSHIPOPPORTUNITY nvarchar(255), 
        REVENUESPLITID uniqueidentifier
    );

    insert into @PAYMENTAPPLIEDTABLE
    -- Payments towards:  Pledge (2), Planned Gift (6), Matching Gift (7), Grant Award (8), Donor Challenge (13)

    select
        INSTALLMENTSPLITPAYMENT.PLEDGEID as ID,
        CONSTITUENT.ID as CONSTITUENTID,
        (  
            select case PLEDGEREVENUE.TRANSACTIONTYPECODE  
            when 3 then  
            CONSTITUENT.NAME + ' - ' +  
            (  
                select   
                C1.NAME   
                from   
                dbo.REVENUEMATCHINGGIFT MG1   
                inner join dbo.REVENUE R1 on R1.ID = MG1.MGSOURCEREVENUEID   
                inner join dbo.CONSTITUENT C1 on C1.ID = R1.CONSTITUENTID   
                where   
                MG1.ID = PLEDGEREVENUE.ID  
            )  
            else   
            CONSTITUENT.NAME  
            end  
        ) as CONSTITUENTNAME,  
        sum(INSTALLMENTSPLITPAYMENT.AMOUNT) as [APPLIED],
        dbo.UFN_PLEDGE_GETBALANCE(INSTALLMENTSPLITPAYMENT.PLEDGEID) as [BALANCE],
        case when REVENUESPLIT.APPLICATIONCODE in (6, 7, 13) then
            dbo.UFN_PLEDGE_GETBALANCE(INSTALLMENTSPLITPAYMENT.PLEDGEID)
        else
            0.00
        end as AMOUNTDUE,
        min(INSTALLMENT.DATE) as [DATEDUE],
        case PLEDGEREVENUE.TRANSACTIONTYPECODE when 6 then 9 when 8 then 10 else PLEDGEREVENUE.TRANSACTIONTYPECODE end as REVENUETYPECODE,
        '' as DESCRIPTION,
        1 as SORTORDER,
        --dbo.UFN_DESIGNATION_GETNAME(REVENUESPLIT.DESIGNATIONID) as DESIGNATIONLIST,

    (select VANITYNAME from dbo.DESIGNATION where ID = REVENUESPLIT.DESIGNATIONID) as DESIGNATIONLIST,
        NULL as SPONSORSHIPOPPORTUNITY,
        REVENUESPLIT.ID as REVENUESPLITID
    from dbo.REVENUE
    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
    inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
    inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
    left join dbo.INSTALLMENT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
    left join dbo.REVENUE as [PLEDGEREVENUE] on [PLEDGEREVENUE].ID = INSTALLMENTSPLITPAYMENT.PLEDGEID
    left join dbo.CONSTITUENT on CONSTITUENT.ID = [PLEDGEREVENUE].CONSTITUENTID
    left join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
    where REVENUE.ID = @REVENUEID and REVENUESPLIT.APPLICATIONCODE in (2, 6, 7, 8, 13)
    group by 
        REVENUESPLIT.ID,
        INSTALLMENTSPLITPAYMENT.PLEDGEID,
        REVENUESPLIT.APPLICATIONCODE,
        PLEDGEREVENUE.TRANSACTIONTYPECODE,
        PLEDGEREVENUE.CONSTITUENTID,
        CONSTITUENT.ID,
        REVENUESPLIT.DESIGNATIONID,
        CONSTITUENT.NAME,
        PLEDGEREVENUE.ID

    union all
    -- recurring gift payments

    select                 
        TEMPLATE.ID as ID,
        CONSTITUENT.ID as CONSTITUENTID,
        CONSTITUENT.NAME,  
        REVENUESPLIT.AMOUNT as APPLIED,
        TEMPLATESPLIT.AMOUNT as BALANCE,
        0.00 as AMOUNTDUE,
        RECURRINGGIFTACTIVITY.SCHEDULEDATE as DATEDUE,
        TEMPLATE.TRANSACTIONTYPECODE as REVENUETYPECODE,
        '' as DESCRIPTION,
        1 as SORTORDER,
        --dbo.UFN_DESIGNATION_GETNAME(REVENUESPLIT.DESIGNATIONID) as DESIGNATIONLIST,

        (select VANITYNAME from dbo.DESIGNATION where ID = REVENUESPLIT.DESIGNATIONID) as DESIGNATIONLIST,
        (select coalesce((select NAME from SPONSORSHIPOPPORTUNITYCHILD where ID=SPONSORSHIP.SPONSORSHIPOPPORTUNITYID),  
                     (select NAME from SPONSORSHIPOPPORTUNITYPROJECT where ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID)) opportunity 
            from dbo.SPONSORSHIP 
            inner join dbo.SPONSORSHIPOPPORTUNITY OPP on OPP.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
            where SPONSORSHIP.ID = dbo.UFN_SPONSORSHIP_ID_FROM_REVENUEID( TEMPLATE.ID)
            and SPONSORSHIP.ISMOSTRECENTFORCOMMITMENT = 1) as SPONSORSHIPOPPORTUNITY,
        REVENUESPLIT.ID as REVENUESPLITID
    from dbo.REVENUE
    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
    inner join dbo.RECURRINGGIFTACTIVITY on PAYMENTREVENUEID = REVENUESPLIT.ID
    inner join dbo.REVENUE TEMPLATE on TEMPLATE.ID = RECURRINGGIFTACTIVITY.SOURCEREVENUEID
    left outer join dbo.REVENUESPLIT TEMPLATESPLIT on TEMPLATESPLIT.ID = TEMPLATE.ID and TEMPLATESPLIT.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
    left join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
    where REVENUE.ID = @REVENUEID and REVENUESPLIT.APPLICATIONCODE  = 3

    union all

    -- Membership

    select
        MEMBERSHIP.ID as ID,
        CONSTITUENT.ID,  
        CONSTITUENT.NAME,    
        REVENUESPLIT.AMOUNT as [APPLIED], 
        0 as BALANCE,
        0 as AMOUNTDUE,  
        MEMBERSHIP.EXPIRATIONDATE,  
        5 as REVENUETYPECODE, --Membership  

        dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID) as description,
        1 as SORTORDER,
        '' as DESIGNATIONLIST,  
        null as SPONSORSHIPOPPORTUNITY,
        REVENUESPLIT.ID as REVENUESPLITID
    from dbo.MEMBERSHIP
    inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
    inner join dbo.REVENUESPLIT on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
    inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
    inner join dbo.CONSTITUENT on MEMBERSHIP.GIVENBYID = CONSTITUENT.ID  
    inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID 
    where REVENUE.ID = @REVENUEID and REVENUESPLIT.APPLICATIONCODE = 5

    union all

    -- Event Reg Payments

    select
        REGISTRANT.ID as ID,
        CONSTITUENT.ID as CONSTITUENTID,
        CONSTITUENT.NAME as CONSTITUENTNAME,
        REVENUESPLIT.AMOUNT as [APPLIED],
        dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as [BALANCE],
        dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as AMOUNTDUE,
        EVENT.STARTDATE as [DATEDUE],
        6 as [REVENUETYPECODE],
        EVENT.NAME as [DESCRIPTION],
        1 as SORTORDER,
        '' as DESIGNATIONLIST,  
        null as SPONSORSHIPOPPORTUNITY,
        REVENUESPLIT.ID as REVENUESPLITID
    from dbo.REGISTRANT
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REGISTRANT.CONSTITUENTID
    inner join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID
    inner join dbo.EVENTREGISTRANTPAYMENT as [PAYMENT] on [PAYMENT].REGISTRANTID = REGISTRANT.ID
    inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = [PAYMENT].PAYMENTID
    inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
    where REVENUE.ID = @REVENUEID and 
    REVENUESPLIT.APPLICATIONCODE = 1;

    insert into @PAYMENTAPPLIEDTABLE
    select REVENUEID,
           CONSTITUENTID,
           CONSTITUENTNAME,
           APPLIED,
           BALANCE,
           AMOUNTDUE,
           DATEDUE,
           REVENUETYPECODE,
           DESCRIPTION,
           SORTORDER,
           DESIGNATIONLIST,
           SPONSORSHIPOPPORTUNITY,
           null as REVENUESPLITID
    from dbo.UFN_CONSTITUENTUNREALIZEDREVENUE(@CONSTITUENTID, @LOADALL, @CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) OPENCOMMITMENTS
    where not exists (select 'X' from @PAYMENTAPPLIEDTABLE APPLIED where APPLIED.REVENUEID = OPENCOMMITMENTS.REVENUEID);

    select REVENUEID,
           CONSTITUENTID,
           CONSTITUENTNAME,
           APPLIED,
           BALANCE,
           AMOUNTDUE,
           DATEDUE,
           REVENUETYPECODE,
           DESCRIPTION,
           SORTORDER,
           DESIGNATIONLIST,
           SPONSORSHIPOPPORTUNITY, 
           REVENUESPLITID
    from @PAYMENTAPPLIEDTABLE;