USP_REVENUEBATCH_VALIDATEREVENUESTREAMS

            CREATE procedure dbo.USP_REVENUEBATCH_VALIDATEREVENUESTREAMS
            (
                @AMOUNT money,
                @REVENUESTREAMS xml,
                @PAYMENTMETHODCODE tinyint,
                @TRANSACTIONCURRENCYID uniqueidentifier = null, -- The transaction currency of the main batch row.

                @REVENUEDATE datetime = null,
                @BASECURRENCYID uniqueidentifier = null,
                @BASEEXCHANGERATEID uniqueidentifier = null
            )
            as 
            set nocount on;

            if @TRANSACTIONCURRENCYID is null
                set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

            if @REVENUEDATE is null
                set @REVENUEDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

            if @REVENUESTREAMS is null
                return 0;

            declare @STREAMSTABLE table
            (
        ID uniqueidentifier,
                APPLICATIONID uniqueidentifier,
                TYPECODE tinyint,
                BALANCE money,
                APPLIED money,
                APPLICATIONCURRENCYID uniqueidentifier,
                AMOUNTDUE money
            );

            insert into @STREAMSTABLE(ID, APPLICATIONID, TYPECODE, BALANCE, APPLIED, APPLICATIONCURRENCYID, AMOUNTDUE)
                select
          ID,
                    APPLICATIONID,
                    TYPECODE,
                    BALANCE,
                    APPLIED,                    -- in the transaction currency of the main row

                    APPLICATIONCURRENCYID,
                    AMOUNTDUE
                from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS)
        where ((APPLIED > 0 and TYPECODE = 2) or TYPECODE <> 2);    

            if @AMOUNT < (select sum(APPLIED) from @STREAMSTABLE)
            begin
                raiserror('The total amount must be at least equal to the sum of the amounts applied to commitments.',13,2); 
                return 2; 
            end

            -- Check if the payment has been applied to any recurring gift which is not active

            -- TYPECODE : 2 = Recurring gift

            -- STATUSCODE : 0 = Active, 1 = Held, 2 = Terminated, 3 = Canceled

            if(
                exists(
                    select 1 
                    from dbo.REVENUESCHEDULE 
                    where ID in (select APPLICATIONID from @STREAMSTABLE where TYPECODE = 2) 
                        and STATUSCODE in (1,2,3)
                        )
                )
            begin
                raiserror('BBERR_PAYMENTAPPLIED_INACTIVERECURRINGGIFT', 13, 1);
                return 5; 
            end

            declare STREAMSCURSOR cursor local fast_forward for
                select ID, APPLICATIONID, BALANCE, APPLIED, TYPECODE, APPLICATIONCURRENCYID, AMOUNTDUE from @STREAMSTABLE;

            declare @STREAMID uniqueidentifier;
            declare @APPLICATIONID uniqueidentifier;
            declare @BALANCE money;
            declare @APPLIED money;
            declare @APPLICATIONAPPLIEDAMOUNT money;
            declare @APPLICATIONCURRENCYID uniqueidentifier;
            declare @APPLICATIONEXCHANGERATEID uniqueidentifier;
            declare @TYPECODE tinyint;
            declare @SPLITS xml;
            declare @APPLICATIONCATEGORYCODEID uniqueidentifier;
            declare @APPLICATIONCODE tinyint;
            declare @AMOUNTDUE money


            open STREAMSCURSOR;
            fetch next from STREAMSCURSOR into @STREAMID, @APPLICATIONID, @BALANCE, @APPLIED, @TYPECODE, @APPLICATIONCURRENCYID, @AMOUNTDUE;

            while @@FETCH_STATUS = 0
            begin

                if @TRANSACTIONCURRENCYID = @APPLICATIONCURRENCYID
                begin
                        set @APPLICATIONAPPLIEDAMOUNT = @APPLIED;
                end
                else
                begin
                    if @BASECURRENCYID = @APPLICATIONCURRENCYID
                    begin
                        set @APPLICATIONEXCHANGERATEID = @BASEEXCHANGERATEID
                    end
                    else
                    begin
                        set @APPLICATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID, @APPLICATIONCURRENCYID, @REVENUEDATE, 0, null);                
                    end
                    set @APPLICATIONAPPLIEDAMOUNT = dbo.UFN_CURRENCY_CONVERT(@APPLIED, @APPLICATIONEXCHANGERATEID);
                end

                --JamesWill WI138046 2011-01-27 Validate negative application amounts

                if @APPLICATIONAPPLIEDAMOUNT < 0
                    raiserror('BBERR_NEGATIVEAPPLICATIONAMOUNT', 13, 1);

                if @TYPECODE <> 5 and @TYPECODE <> 2 and @APPLICATIONAPPLIEDAMOUNT > @BALANCE --JamesWill CR269274-030707 2007/03/19 Don't do this check for Recurring Gifts (@TYPECODE = 2)

                begin
                    raiserror('You cannot apply an amount greater than the balance for a commitment.', 13, 3);
                    return 3;
                end

                if @TYPECODE = 2 and @BALANCE = 0 -- Josh Jones WI 491134 5/1/2015 Don't allow payments towards recurring gifts without a balance

                begin
                    raiserror('You cannot apply a payment towards a recurring gift without a balance.', 13, 3);
                    return 3;
                end

                --If an amount has been applied to a commitment, validate the GL mappings.

                if @APPLIED > 0 
                begin
                    if @APPLICATIONID='9B9C1DC8-7960-4D31-A0BC-8199AB7F94DA' and @STREAMID is not null
                    begin --handle pledge added from apply dialog

                        if exists(select top 1 DESIGNATION.ID from dbo.BATCHREVENUEAPPLICATION BRA inner join dbo.BATCHREVENUEAPPLICATIONPLEDGESPLIT BRAPS on BRA.BATCHREVENUEAPPLICATIONPLEDGEID=BRAPS.BATCHREVENUEAPPLICATIONPLEDGEID    inner join     dbo.DESIGNATION on BRAPS.DESIGNATIONID=DESIGNATION.ID where BRA.ID=@STREAMID and DESIGNATION.ISACTIVE=0) 
                        begin
                            raiserror('BBERR_CANNOTPAYINACTIVEDESIGNATIONS : Revenue cannot be added to inactive designations.', 13, 2);
                            return 2;
                        end
                    end
                    else 
                    begin
                        if exists(
                            select 1
                            from
                                dbo.FINANCIALTRANSACTION
                                inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTION.ID
                                inner join dbo.DESIGNATION on INSTALLMENTSPLIT.DESIGNATIONID = DESIGNATION.ID
                            where
                                FINANCIALTRANSACTION.ID = @APPLICATIONID
                                and dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(INSTALLMENTSPLIT.ID) > 0
                                and DESIGNATION.ISACTIVE = 0) 
                        begin
                            raiserror('BBERR_CANNOTPAYINACTIVEDESIGNATIONS : Revenue cannot be added to inactive designations.', 13, 2);
                            return 2;
                        end         
                    end

                    --WI378664 - Check that we do not add revenue to a RG that has inactive designations. This includes Sponsorships

                    if @TYPECODE = 2
                    begin
                        if exists (
                                select 1
                                from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                                inner join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
                                where REVENUESPLIT.FINANCIALTRANSACTIONID = @APPLICATIONID and REVENUESPLIT_EXT.TYPECODE in (0, 4, 9, 17) and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1 and DESIGNATION.ISACTIVE = 0
                                )
                        begin
                            raiserror('BBERR_CANNOTPAYINACTIVEDESIGNATIONS : Revenue cannot be added to inactive designations.', 13, 2);
                            return 2;
                        end
                    end

                    --Validate GL mappings for events

                    if @TYPECODE = 6 
                        exec dbo.USP_EVENT_VALIDATEGLMAPPINGS @APPLICATIONID, 0, @PAYMENTMETHODCODE, 1, 1;
                    else
                    begin
                    --Validate GL mappings for designations


                        select @APPLICATIONCODE = case @TYPECODE
                            when 1 then 2 -- pledge payment

                            when 2 then 3 -- recurring gift payment

                            when 3 then 7 -- matching gift payment

                            when 4 then 6 -- planned gift payment

                            when 5 then 5 -- membership

                            when 9 then 8 -- grant award

              when 10 then 13 -- donor challenge

                            else 0 end;

                        --This assumes that each split for the application has the same revenue category

                        select top 1 
                            @APPLICATIONCATEGORYCODEID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID 
                        from 
                            dbo.REVENUESPLIT
                        left join 
                            dbo.REVENUECATEGORY on REVENUESPLIT.ID = REVENUECATEGORY.ID
                        where 
                            REVENUESPLIT.REVENUEID = @APPLICATIONID;   

                        set @SPLITS = (
                            select
                                REVENUESPLIT.DESIGNATIONID,
                                @APPLICATIONCODE as APPLICATIONCODE,
                                0 as TYPECODE
                            from dbo.REVENUESPLIT
                            where REVENUESPLIT.REVENUEID = @APPLICATIONID
                            for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
                        )        

                        if @SPLITS is not null
                            exec dbo.USP_REVENUESPLITS_VALIDATEGLMAPPINGS @SPLITS, 0, @PAYMENTMETHODCODE, @APPLICATIONCATEGORYCODEID;

                    end
                end

                if (@APPLIED > 0) and (@TYPECODE = 6) and (exists (select top(1) ID from dbo.BATCHREVENUEREGISTRANT where ID = @APPLICATIONID))
                begin                
                    if exists(
                        select top(1)
                            REGISTRANT.ID
                        from
                            dbo.REGISTRANT
                        inner join
                            dbo.BATCHREVENUEREGISTRANT on (BATCHREVENUEREGISTRANT.CONSTITUENTID = REGISTRANT.CONSTITUENTID) and (BATCHREVENUEREGISTRANT.EVENTID = REGISTRANT.EVENTID)
                        where
                            BATCHREVENUEREGISTRANT.ID = @APPLICATIONID and REGISTRANT.ID <> @APPLICATIONID
                    ) or exists (
                        select top(1)
                            REGISTRANT.ID
                        from
                            dbo.REGISTRANT
                        inner join
                            dbo.BATCHREVENUEREGISTRANT on (BATCHREVENUEREGISTRANT.CONSTITUENTID = REGISTRANT.CONSTITUENTID) and (BATCHREVENUEREGISTRANT.EVENTID = REGISTRANT.EVENTID)
                        where
                            BATCHREVENUEREGISTRANT.GUESTOFREGISTRANTID = @APPLICATIONID and REGISTRANT.ID <> @APPLICATIONID
                    )
                    begin
                        raiserror('A registrant or guest of a registrant has already been registered for the event', 13, 4);
                        return 4;
                    end

                    -- if an event registration was created in batch and two payment rows applied toward it, the second row wouldn't be

                    -- caught by normal validation.  We chase that down here and raise an error similar to the one above:

                    declare @REGISTRANTCONSTITUENTID uniqueidentifier;
                    select @REGISTRANTCONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID from dbo.BATCHREVENUEREGISTRANT where BATCHREVENUEREGISTRANT.ID = @APPLICATIONID;

                    declare @REGISTRANTID uniqueidentifier;
                    select
                        @REGISTRANTID = REGISTRANT.ID
                    from
                        dbo.BATCHREVENUEREGISTRANT
                        inner join dbo.REGISTRANT 
                            on BATCHREVENUEREGISTRANT.EVENTID = REGISTRANT.EVENTID
                            and @REGISTRANTCONSTITUENTID = REGISTRANT.CONSTITUENTID
                    where
                        BATCHREVENUEREGISTRANT.ID = @APPLICATIONID;

                    if (@REGISTRANTID is not null)
                    begin
                        if (@APPLICATIONAPPLIEDAMOUNT > dbo.UFN_EVENTREGISTRANT_GETBALANCE(@REGISTRANTID))
                        begin
                            raiserror('You cannot apply an amount greater than the balance for a commitment.', 13, 3);
                            return 3;
                        end
                    end
                end

                fetch next from STREAMSCURSOR into @STREAMID, @APPLICATIONID,@BALANCE, @APPLIED, @TYPECODE, @APPLICATIONCURRENCYID, @AMOUNTDUE;
            end

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

            close STREAMSCURSOR;
            deallocate STREAMSCURSOR;

            return 0;