USP_REVENUESPLIT_VALIDATESPLITS

            CREATE procedure dbo.USP_REVENUESPLIT_VALIDATESPLITS
            (
                @SPLITS xml,  
                @REVENUEAMOUNT money,
                @TRANSACTIONTYPECODE int = 0,
                @REVENUEID uniqueidentifier = null,
                @TRANSACTIONCURRENCYID uniqueidentifier = null,
                @VALIDATETRANSACTIONCURRENCY bit = 1
            )
            as  

                set nocount on; 

                if @SPLITS is null 
                begin 
                    raiserror('Please enter at least one designation.', 13, 1); 
                    return 1; 
                end 

                declare @SUM money; 
                declare @ABSSUM money; 
                declare @NONNULL_DESIGSPLITCOUNT int; 
                declare @DISTINCTDESIGSPLITCOUNT int; 
                declare @TOTALSPLITCOUNT int; 
                declare @VALIDCOUNT int;

                --Set currency parameters for backwards compatibility

                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                if @TRANSACTIONCURRENCYID is null
                    set @TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID;

                if @VALIDATETRANSACTIONCURRENCY is null
                    set @VALIDATETRANSACTIONCURRENCY = 1;

                declare @SPLITSTABLE table 
                ( 
                    AMOUNT money, 
                    DESIGNATIONID uniqueidentifier,
                    ISVALID bit,
                    TRANSACTIONCURRENCYID uniqueidentifier,
                    APPLICATIONCODE tinyint
                ); 

                -- MRF: WI 36670

                declare @OLDINACTIVEDESIGNATIONTABLE table
                (
                    DESIGNATIONID uniqueidentifier
                );

                insert into @OLDINACTIVEDESIGNATIONTABLE(
                    DESIGNATIONID
                )
                select 
                    distinct REVENUESPLIT_EXT.DESIGNATIONID 
                from 
                    dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                where 
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1

                insert into @SPLITSTABLE(
                    AMOUNT, 
                    DESIGNATIONID, 
                    ISVALID,
                    TRANSACTIONCURRENCYID,
                    APPLICATIONCODE
                ) 
                select 
                    [SPLITS].AMOUNT, 
                    [SPLITS].DESIGNATIONID, 
                    case 
                        when (DESIGNATION.ISACTIVE = 1 or [OLD].DESIGNATIONID is not null) 
                            then 1 
                        else 0 
                    end as ISVALID,
                    case
                        when [SPLITS].TRANSACTIONCURRENCYID is null then
                            @ORGANIZATIONCURRENCYID
                        else
                            [SPLITS].TRANSACTIONCURRENCYID
                    end,
                    coalesce([SPLITS].APPLICATIONCODE, null) as APPLICATIONCODE
                from 
                    dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) as [SPLITS]
                    inner join dbo.DESIGNATION on [SPLITS].DESIGNATIONID = DESIGNATION.ID
                    left join @OLDINACTIVEDESIGNATIONTABLE as [OLD] on [OLD].DESIGNATIONID = DESIGNATION.ID

                -- AdamBu 3/17/10 - Ensure that the split's transaction currency is the same as its revenue.

                if (@VALIDATETRANSACTIONCURRENCY = 1) and exists(
                    select 1
                    from @SPLITSTABLE
                    where TRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID
                )
                begin
                    raiserror('A split''s transaction currency must match that of its revenue.',13,7); 
                    return 7; 
                end

                select 
                    @SUM = sum(AMOUNT),
                    @ABSSUM = sum(abs(AMOUNT)),
                    @TOTALSPLITCOUNT=count(*),
                    @DISTINCTDESIGSPLITCOUNT= (select count(1) from (select distinct DESIGNATIONID, APPLICATIONCODE from @SPLITSTABLE) as DISTINCTCOUNT),
                    @NONNULL_DESIGSPLITCOUNT=count(DESIGNATIONID),
                    @VALIDCOUNT = (select count(*) from @SPLITSTABLE where ISVALID = 1)
                from @SPLITSTABLE; 

                if @VALIDCOUNT <> @TOTALSPLITCOUNT
                begin
                    raiserror('BBERR_CANNOTPAYINACTIVEDESIGNATIONS : Revenue cannot be added to inactive designations.', 13, 6);
                    return 6;
                end

                if @SUM <> @ABSSUM  
                begin 
                    raiserror('Designation amount cannot be negative.',13,8); 
                    return 8; 
                end 

                if @SUM <> @REVENUEAMOUNT 
                begin 
                    raiserror('The total amount must equal the sum of the designations.',13,2); 
                    return 2; 
                end 

                if @TOTALSPLITCOUNT = 0 
                begin 
                    raiserror('Please enter at least one designation.', 13, 3); 
                    return 3; 
                end 

                if @NONNULL_DESIGSPLITCOUNT <> @TOTALSPLITCOUNT  
                begin 
                    raiserror('Every distribution must have a designation.', 13, 4); 
                    return 4; 
                end 

                --select  

                --    @NODESIGSPLITCOUNT = count(AMOUNT)  

                --from @SPLITSTABLE 

                --where DESIGNATIONID is null; 

                -- 

                --if @NODESIGSPLITCOUNT > 0 

                --begin 

                --    raiserror('At least one column for each split must be non-null.', 13, 1); 

                --    return 0; 

                --end 



                --JamesWill 03/14/2006 CR237785-031306 

                --MMR designations may be non-unique for payments now that pledgepayments and gifts will share the revenueid

                if @TRANSACTIONTYPECODE <> 0 and @TOTALSPLITCOUNT <> @DISTINCTDESIGSPLITCOUNT 
                begin 
                    raiserror('Duplicate designations cannot be specified.', 13, 5); 
                    return 5; 
                end 

                return 0;