USP_REVENUELIFECYCLEREVENUECRITERIA_UPDATEVALIDREVENUEFUNCTION

CREATE procedure dbo.USP_REVENUELIFECYCLEREVENUECRITERIA_UPDATEVALIDREVENUEFUNCTION
as
begin
    set nocount on;

    declare @FUNCTIONSQL nvarchar(max);
    declare @WHERECLAUSE nvarchar(max) = '';
    declare @REVENUETYPECODE tinyint;
    declare @CONSIDERCLAIMEDAMOUNT bit;
    declare @CONSIDERRECOGNITION bit;

    select top 1 @CONSIDERRECOGNITION = CONSIDERRECOGNITION from dbo.REVENUELIFECYCLECRITERIA;

    begin try    
        set @FUNCTIONSQL = 
            'alter function dbo.UFN_REVENUELIFECYCLE_GETVALIDREVENUE() ' + char(13)
            + 'returns table as ' + char(13)
            + '    return ( ' + char(13)
            + '        select ' + char(13)
            + '            REVENUE.ID as REVENUEID, ' + char(13)
            + '            REVENUE.CONSTITUENTID, ' + char(13)
            + '            REVENUESPLIT.ID as REVENUESPLITID, ' + char(13)
            + '            REVENUE.[DATE], ' + char(13)
            + '            REVENUE.TRANSACTIONTYPECODE, ' + char(13)
            + '            REVENUESPLIT.APPLICATIONCODE, ' + char(13)
            + '            REVENUESPLIT.ORGANIZATIONAMOUNT - case when REVENUE.TRANSACTIONTYPECODE in (1,6) then (coalesce((select sum(INSTALLMENTSPLITWRITEOFF.ORGANIZATIONAMOUNT) from dbo.INSTALLMENTSPLITWRITEOFF inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID where INSTALLMENTSPLIT.PLEDGEID = REVENUESPLIT.REVENUEID), 0)) else 0 end as AMOUNT ' + char(13)
            + '        from ' + char(13)
            + '            dbo.REVENUE with (nolock) ' + char(13)
            + '            inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID ' + char(13)
            + '        where ' + char(13);

        declare REVENUECRITERIACURSOR cursor local fast_forward for
        select
            REVENUETYPECODE,
            CONSIDERCLAIMEDAMOUNT
        from dbo.REVENUELIFECYCLEREVENUECRITERIA;

        open REVENUECRITERIACURSOR;

        fetch next from REVENUECRITERIACURSOR into @REVENUETYPECODE, @CONSIDERCLAIMEDAMOUNT;

        while (@@FETCH_STATUS = 0)
            begin
                if len(@WHERECLAUSE) > 0
                    set @WHERECLAUSE = @WHERECLAUSE + '        or ';

                set @WHERECLAUSE = @WHERECLAUSE +
                    case 
                        when @REVENUETYPECODE = 0 then 
                            '(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 0)    -- Donations ' + char(13)

                            + '        or (REVENUE.TRANSACTIONTYPECODE = 5 and REVENUESPLIT.TYPECODE = 0)    -- Order Donations ' + char(13)


                        when @REVENUETYPECODE = 1 and @CONSIDERCLAIMEDAMOUNT = 0 then
                            '(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 2)    -- Pledge payments ' + char(13)


                        when @REVENUETYPECODE = 1 and @CONSIDERCLAIMEDAMOUNT = 1 then
                            '(REVENUE.TRANSACTIONTYPECODE = 1) -- Pledges ' + char(13)


                        when @REVENUETYPECODE = 2 then
                            '(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 3)    -- Recurring gift payments ' + char(13)


                        when @REVENUETYPECODE = 3 then
                            '(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 1)    -- Event registrations ' + char(13)


                        when @REVENUETYPECODE = 4 then
                            '(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 9)    -- Admissions ' + char(13)


                        when @REVENUETYPECODE = 5 then
                            '(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 5)    -- Memberships ' + char(13)


                        when @REVENUETYPECODE = 6 and @CONSIDERCLAIMEDAMOUNT = 0 then
                            '(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 7)    -- Matching gift claim payments ' + char(13)


                        when @REVENUETYPECODE = 6 and @CONSIDERCLAIMEDAMOUNT = 1 then
                            '(REVENUE.TRANSACTIONTYPECODE = 3 and REVENUESPLIT.APPLICATIONCODE = 0)    -- Matching gift claims ' + char(13)


                        when @REVENUETYPECODE = 7 then
                            '(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 4)    -- Other ' + char(13)


                        when @REVENUETYPECODE = 8 and @CONSIDERCLAIMEDAMOUNT = 0 then
                            '(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 6)    -- Planned gift payments ' + char(13)


                        when @REVENUETYPECODE = 8 and @CONSIDERCLAIMEDAMOUNT = 1 then
                            '(REVENUE.TRANSACTIONTYPECODE = 4 and REVENUESPLIT.APPLICATIONCODE = 0)    -- Planned gifts ' + char(13)


                        when @REVENUETYPECODE = 9 and @CONSIDERCLAIMEDAMOUNT = 0 then
                            '(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 13) -- Donor challenge claim payments ' + char(13)


                        when @REVENUETYPECODE = 9 and @CONSIDERCLAIMEDAMOUNT = 1 then
                            '(REVENUE.TRANSACTIONTYPECODE = 8 and REVENUESPLIT.APPLICATIONCODE = 0)    -- Donor challenge claims ' + char(13)


                        when @REVENUETYPECODE = 10 then
                            '(REVENUE.TRANSACTIONTYPECODE = 7 and REVENUESPLIT.APPLICATIONCODE = 0) -- Auction item donations ' + char(13)


                        when @REVENUETYPECODE = 11 then
                            '(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 12) -- Auction item purchases ' + char(13)


                        when @REVENUETYPECODE = 12 and @CONSIDERCLAIMEDAMOUNT = 0 then
                            '(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 8) -- Grant award payments ' + char(13)


                        when @REVENUETYPECODE = 12 and @CONSIDERCLAIMEDAMOUNT = 1 then
                            '(REVENUE.TRANSACTIONTYPECODE = 6 and REVENUESPLIT.APPLICATIONCODE = 8) -- Grant awards ' + char(13)


                        else ''
                    end;            

                fetch next from REVENUECRITERIACURSOR into @REVENUETYPECODE, @CONSIDERCLAIMEDAMOUNT;
            end

            --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

            close REVENUECRITERIACURSOR;
            deallocate REVENUECRITERIACURSOR;

        set @FUNCTIONSQL = @FUNCTIONSQL + @WHERECLAUSE 

        if @CONSIDERRECOGNITION = 1 begin
        set @FUNCTIONSQL = @FUNCTIONSQL 
            + '        union ' + char(13)
            + '        select ' + char(13)
            + '            REVENUE.ID as REVENUEID, ' + char(13)
            + '            REVENUERECOGNITION.CONSTITUENTID, ' + char(13)
            + '            REVENUESPLIT.ID as REVENUESPLITID, ' + char(13)
            + '            REVENUE.[DATE], ' + char(13)
            + '            REVENUE.TRANSACTIONTYPECODE, ' + char(13)
            + '            REVENUESPLIT.APPLICATIONCODE, ' + char(13)
            + '            REVENUESPLIT.ORGANIZATIONAMOUNT - case when REVENUE.TRANSACTIONTYPECODE in (1,6) then (coalesce((select sum(INSTALLMENTSPLITWRITEOFF.ORGANIZATIONAMOUNT) from dbo.INSTALLMENTSPLITWRITEOFF inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID where INSTALLMENTSPLIT.PLEDGEID = REVENUESPLIT.REVENUEID), 0)) else 0 end as AMOUNT ' + char(13)
            + '        from ' + char(13)
            + '            dbo.REVENUE with (nolock) ' + char(13)
            + '            inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID ' + char(13)
            + '            inner join dbo.REVENUERECOGNITION with (nolock) on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID ' + char(13)
            + '        where ' + @WHERECLAUSE + char(13);
        end;

        set @FUNCTIONSQL = @FUNCTIONSQL + ' )';

        exec sp_sqlexec @FUNCTIONSQL;
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch
end