USP_REVENUESPLITS_VALIDATEGLMAPPINGS

            CREATE procedure dbo.USP_REVENUESPLITS_VALIDATEGLMAPPINGS
            (
                @SPLITS xml, 
                @REVENUETRANSACTIONTYPECODE tinyint,
                @PAYMENTMETHODCODE tinyint,
                @CATEGORYCODEID uniqueidentifier,
                @ISUNAPPLIEDMATCHINGGIFTSPLITS bit = 0
            )
            as
                set nocount on;

                declare @REVENUECATEGORYACCOUNT nvarchar(100);
                declare @REVENUECATEGORY nvarchar(100);
                declare @ERRORMESSAGE nvarchar(max)
                declare @DESIGNATIONID uniqueidentifier;
                declare @DESIGNATIONNAME nvarchar(512);
                declare @MAPPINGID uniqueidentifier;
                declare @ACCOUNTTYPE nvarchar(100);

                declare @SPLITSTABLE table (
                    DESIGNATIONID uniqueidentifier,
                    APPLICATIONCODE tinyint,
                    TYPECODE tinyint
                )

                if @ISUNAPPLIEDMATCHINGGIFTSPLITS = 1
                    begin
                        insert into @SPLITSTABLE(DESIGNATIONID,APPLICATIONCODE,TYPECODE)
                        select
                            T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') AS 'DESIGNATIONID',
                            7 as APPLICATIONCODE, -- Matching Gift

                            0 as TYPECODE -- Gift

                            from @SPLITS.nodes('/UNAPPLIEDMATCHINGGIFTSPLITS/ITEM') T(c)
                    end

                else
                    begin
                        insert into @SPLITSTABLE(DESIGNATIONID,APPLICATIONCODE,TYPECODE)
                            select 
                                DESIGNATIONID, 
                                case @ISUNAPPLIEDMATCHINGGIFTSPLITS when 0 then APPLICATIONCODE else 7 end as APPLICATIONCODE, 
                                case @ISUNAPPLIEDMATCHINGGIFTSPLITS when 0 then TYPECODE else 0 end as TYPECODE
                            from 
                                dbo.UFN_REVENUE_GETSPLITS_FROMITEMLISTXML(@SPLITS)
                    end

                if dbo.UFN_GLACCOUNT_EXISTS() = 0
                    begin
                        -- 1. Validate that the revenue category has been mapped

                        if @ISUNAPPLIEDMATCHINGGIFTSPLITS = 0
                            begin
                                select 
                                    @REVENUECATEGORYACCOUNT = coalesce(MAP.ACCOUNTCODE, ''),
                                    @REVENUECATEGORY = MAP.REVENUECATEGORYNAME
                                from 
                                    dbo.GLREVENUECATEGORYMAPPING MAP 
                                where
                                    ID = @CATEGORYCODEID;

                                --The revenue record does not have a category, so use the empty account code from config

                                if @REVENUECATEGORYACCOUNT is null
                                    select @REVENUECATEGORY = 'default', @REVENUECATEGORYACCOUNT = coalesce((select top 1 EMPTYREVENUECATEGORYACCOUNTCODE from dbo.GLPREFERENCEINFO), '');

                                --If the revenue category account is not mapped, raise an error 

                                if coalesce(@REVENUECATEGORYACCOUNT, '') = '' and exists (select 1 from @SPLITSTABLE SPLITS where SPLITS.TYPECODE <> 1)
                                begin
                                    raiserror('GL account code is not defined for the %s revenue category.', 13, 1, @REVENUECATEGORY);
                                    return;
                                end
                            end

                        -- 2. Validate that the designations contained in the splits collection are mapped

                        select top 1 
                            @DESIGNATIONID = DESIGNATION.ID, 
                            @DESIGNATIONNAME = DESIGNATION.NAME
                        from 
                            dbo.DESIGNATION 
                        inner join 
                            @SPLITSTABLE SPLITS on DESIGNATION.ID = SPLITS.DESIGNATIONID
                        where 
                            ACCOUNTNUMBER = ''

                        if @DESIGNATIONID is not null
                        begin
                            raiserror('GL account/fund code is not defined for the %s designation.', 13, 1, @DESIGNATIONNAME);
                            return;
                        end

                        -- 3. Validate that the payment method revenue type mappings have account types defined for them

                        select top 1 @MAPPINGID = (
                            select top 1 MAP.ID
                            from dbo.GLPAYMENTMETHODREVENUETYPEMAPPING AS MAP 
                            where MAP.REVENUETRANSACTIONTYPECODE = @REVENUETRANSACTIONTYPECODE 
                            and MAP.PAYMENTMETHODCODE = @PAYMENTMETHODCODE 
                            and MAP.REVENUESPLITTYPECODE = SPLITS.TYPECODE 
                            and MAP.APPLICATIONCODE = SPLITS.APPLICATIONCODE
                            and MAP.GLACCOUNTTYPEMAPPINGID is null)
                        from 
                             @SPLITSTABLE SPLITS

                        if @MAPPINGID is not null
                        begin
                            raiserror('Payment method and revenue type of the revenue record does not map to a valid GL account type.', 13, 1);
                            return;
                        end

                        -- 4. Validate that the account type codes are mapped

                        select top 1 @ACCOUNTTYPE = 
                            (select top 1 GLACCOUNTTYPEMAPPING.ACCOUNTTYPE
                            from dbo.GLPAYMENTMETHODREVENUETYPEMAPPING AS MAP 
                            left join dbo.GLACCOUNTTYPEMAPPING on MAP.GLACCOUNTTYPEMAPPINGID = GLACCOUNTTYPEMAPPING.ID
                            where MAP.REVENUETRANSACTIONTYPECODE = @REVENUETRANSACTIONTYPECODE and MAP.PAYMENTMETHODCODE = @PAYMENTMETHODCODE and MAP.REVENUESPLITTYPECODE = SPLITS.TYPECODE and MAP.APPLICATIONCODE = SPLITS.APPLICATIONCODE
                            and coalesce(GLACCOUNTTYPEMAPPING.GLCODE, '') = '')
                        from 
                             @SPLITSTABLE SPLITS; 

                        if @ACCOUNTTYPE is not null
                        begin
                            raiserror('GL account code is not defined for the %s account type.', 13, 1, @ACCOUNTTYPE);
                            return;
                        end
                    end
                return 0;