UFN_REVENUE_GENERATEGLACCOUNT_STANDARD

            CREATE function DBO.UFN_REVENUE_GENERATEGLACCOUNT_STANDARD
            (
                @REVENUESPLITID as uniqueidentifier,
                @REVENUETRANSACTIONTYPECODE tinyint,
                @REVENUESPLITTYPECODE tinyint,
                @APPLICATIONCODE tinyint,
                @PAYMENTMETHODCODE tinyint,
                @DESIGNATIONID uniqueidentifier
            )
            returns 
                @DATA table (ACCOUNTSTRING nvarchar(255), PROJECTCODE nvarchar(255), TRANSACTIONTYPECODE tinyint, ERRORMESSAGE nvarchar(max), ACCOUNTID uniqueidentifier)
            as
            begin
                declare @ACCOUNTNUMBER nvarchar(100);
                declare @PROJECTCODE nvarchar(255);
                declare @DESIGNATIONNAME nvarchar(100);
                declare @ERRORMESSAGE nvarchar(max);
                declare @RECORDTYPE nvarchar(11);
                declare @RECORDNAME nvarchar(250);
                declare @REVENUECATEGORYACCOUNT nvarchar(100);
                declare @REVENUECATEGORY nvarchar(100);
                declare @SPLITAPPLICATIONCODE tinyint;
                declare @SPLITTYPECODE tinyint;
                declare @ACCOUNTID uniqueidentifier;                

                -- We need to get the application code from the split in case that the @APPLICATIONCODE parameter does not match the 

                -- split's application code, for example the parameter is 200 for 'All'. This is needed to determine whether we need 

                -- to execute event registration or membership code below.

                select 
                    @SPLITAPPLICATIONCODE = APPLICATIONCODE,
                    @SPLITTYPECODE = TYPECODE
                from 
                    dbo.REVENUESPLIT 
                where 
                    ID = @REVENUESPLITID;

                declare @DEBITSCREDITS table(
                    ACCOUNT nvarchar(100),
                    GLACCOUNTTYPEMAPPINGID uniqueidentifier,
                    TRANSACTIONTYPECODE tinyint,
                    ERRORMESSAGE nvarchar(max)
                )

                insert into @DEBITSCREDITS 
                    (ACCOUNT,GLACCOUNTTYPEMAPPINGID,TRANSACTIONTYPECODE,ERRORMESSAGE)
                select
                    GLACCOUNTTYPEMAPPING.GLCODE,
                    MAP.GLACCOUNTTYPEMAPPINGID,
                    MAP.TRANSACTIONTYPECODE,
                    case when coalesce(GLACCOUNTTYPEMAPPING.GLCODE, '') = '' then 'GL account code is not defined for the ' + GLACCOUNTTYPEMAPPING.ACCOUNTTYPE + ' account type.' else '' end
                from 
                    dbo.GLPAYMENTMETHODREVENUETYPEMAPPING AS MAP with (nolock)
                left join 
                    dbo.GLACCOUNTTYPEMAPPING on MAP.GLACCOUNTTYPEMAPPINGID = GLACCOUNTTYPEMAPPING.ID
                where 
                    MAP.REVENUETRANSACTIONTYPECODE = @REVENUETRANSACTIONTYPECODE and MAP.PAYMENTMETHODCODE = @PAYMENTMETHODCODE and MAP.REVENUESPLITTYPECODE = @REVENUESPLITTYPECODE and MAP.APPLICATIONCODE = @APPLICATIONCODE;

                if @SPLITAPPLICATIONCODE = 1 -- Event registration

                    begin
                        select 
                            @ACCOUNTNUMBER = case when EVENTGLMAPPING.ID is null then '' else ACCOUNTNUMBER end, 
                            @PROJECTCODE = case when EVENTGLMAPPING.ID is null then '' else PROJECTCODE end,
                            @RECORDNAME = EVENT.NAME
                        from dbo.EVENT with (nolock)
                            inner join dbo.REGISTRANT with (nolock) on REGISTRANT.EVENTID = EVENT.ID
                            inner join dbo.EVENTREGISTRANTPAYMENT with (nolock) on EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID
                            left join dbo.EVENTGLMAPPING with (nolock) on EVENT.ID = EVENTGLMAPPING.ID
                        where 
                            EVENTREGISTRANTPAYMENT.PAYMENTID=@REVENUESPLITID;

                        set @REVENUECATEGORYACCOUNT = '';
                        set @RECORDTYPE = 'event';
                    end

                if @SPLITTYPECODE = 2 --Membership

                    begin
                    if @SPLITAPPLICATIONCODE = 5 -- Membership

                        begin
                            select 
                                @ACCOUNTNUMBER = case when MEMBERSHIPGLMAPPING.ID is null then '' else ACCOUNTNUMBER end, 
                                @PROJECTCODE = case when MEMBERSHIPGLMAPPING.ID is null then '' else PROJECTCODE end,
                                @RECORDNAME = MEMBERSHIPPROGRAM.NAME + ' - ' + MEMBERSHIPLEVEL.NAME
                            from dbo.MEMBERSHIPLEVEL with (nolock)
                                inner join dbo.MEMBERSHIPPROGRAM with (nolock) on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                                inner join dbo.MEMBERSHIPTRANSACTION with (nolock) on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                                left join dbo.MEMBERSHIPGLMAPPING with (nolock) on MEMBERSHIPLEVEL.ID = MEMBERSHIPGLMAPPING.ID
                            where 
                                MEMBERSHIPTRANSACTION.REVENUESPLITID=@REVENUESPLITID;

                            set @REVENUECATEGORYACCOUNT = '';
                            set @RECORDTYPE = 'membership';                        
                        end
                    else    -- Membership payment

                        if @SPLITAPPLICATIONCODE = 10 --Order

                        begin
                            declare @MEMBERSHIPLEVELID uniqueidentifier;

                            select @MEMBERSHIPLEVELID = REVENUESPLITORDER.MEMBERSHIPLEVELID
                            from dbo.REVENUESPLITORDER
                            where ID = @REVENUESPLITID;

                            select 
                                @ACCOUNTNUMBER = case when MEMBERSHIPGLMAPPING.ID is null then '' else ACCOUNTNUMBER end, 
                                @PROJECTCODE = case when MEMBERSHIPGLMAPPING.ID is null then '' else PROJECTCODE end,
                                @RECORDNAME = MEMBERSHIPPROGRAM.NAME + ' - ' + MEMBERSHIPLEVEL.NAME
                            from dbo.MEMBERSHIPLEVEL with (nolock)
                                inner join dbo.MEMBERSHIPPROGRAM with (nolock) on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                                left join dbo.MEMBERSHIPGLMAPPING with (nolock) on MEMBERSHIPLEVEL.ID = MEMBERSHIPGLMAPPING.ID
                            where 
                                MEMBERSHIPLEVEL.ID=@MEMBERSHIPLEVELID;

                            set @REVENUECATEGORYACCOUNT = '';
                            set @RECORDTYPE = 'membership';                        
                        end
                    end

                if @SPLITTYPECODE = 5 -- Ticket

                    begin
                        declare @PROGRAMID uniqueidentifier;

                        select @PROGRAMID = REVENUESPLITORDER.PROGRAMID
                        from dbo.REVENUESPLITORDER
                        where ID = @REVENUESPLITID;

                        select 
                            @ACCOUNTNUMBER = case when PROGRAMGLMAPPING.ID is null then '' else ACCOUNTNUMBER end, 
                            @PROJECTCODE = case when PROGRAMGLMAPPING.ID is null then '' else PROJECTCODE end,
                            @RECORDNAME = PROGRAM.NAME 
                        from dbo.PROGRAM 
                            left join dbo.PROGRAMGLMAPPING on PROGRAM.ID = PROGRAMGLMAPPING.ID
                        where PROGRAM.ID = @PROGRAMID;

                        set @REVENUECATEGORYACCOUNT = '';
                        set @RECORDTYPE = 'program';

                    end

                if @SPLITTYPECODE = 6 -- Fee

                    begin
                        declare @FEEID uniqueidentifier;

                        select @FEEID = REVENUESPLITORDER.FEEID
                        from dbo.REVENUESPLITORDER
                        where ID = @REVENUESPLITID;

                        select 
                            @ACCOUNTNUMBER = case when FEEGLMAPPING.ID is null then '' else ACCOUNTNUMBER end, 
                            @PROJECTCODE = case when FEEGLMAPPING.ID is null then '' else PROJECTCODE end,
                            @RECORDNAME = FEE.NAME 
                        from dbo.FEE 
                            left join dbo.FEEGLMAPPING on FEE.ID = FEEGLMAPPING.ID
                        where FEE.ID = @FEEID;

                        set @REVENUECATEGORYACCOUNT = '';
                        set @RECORDTYPE = 'fee';

                    end

                if @SPLITTYPECODE = 7 -- Tax

                    begin
                        declare @TAXID uniqueidentifier;

                        select @TAXID = REVENUESPLITORDER.TAXID
                        from dbo.REVENUESPLITORDER
                        where ID = @REVENUESPLITID;

                        select 
                            @ACCOUNTNUMBER = case when TAXGLMAPPING.ID is null then '' else ACCOUNTNUMBER end, 
                            @PROJECTCODE = case when TAXGLMAPPING.ID is null then '' else PROJECTCODE end,
                            @RECORDNAME = TAX.NAME 
                        from dbo.TAX 
                            left join dbo.TAXGLMAPPING on TAX.ID = TAXGLMAPPING.ID
                        where TAX.ID = @TAXID;

                        set @REVENUECATEGORYACCOUNT = '';
                        set @RECORDTYPE = 'tax';

                    end


                if @ACCOUNTNUMBER is null
                    begin
                        select 
                            @ACCOUNTNUMBER = ACCOUNTNUMBER, 
                            @PROJECTCODE = PROJECTCODE
                            --@DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL1ID

                        from 
                            DBO.DESIGNATION with (nolock)
                        where 
                            DESIGNATION.ID=@DESIGNATIONID;

                        select 
                            @REVENUECATEGORYACCOUNT = coalesce(MAP.ACCOUNTCODE, ''),
                            @REVENUECATEGORY = MAP.REVENUECATEGORYNAME
                        from 
                            dbo.REVENUECATEGORY
                        left join 
                            dbo.GLREVENUECATEGORYMAPPING MAP on REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID = MAP.ID
                        where
                            REVENUECATEGORY.ID = @REVENUESPLITID;

                        -- 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), '');

                        set @RECORDTYPE = 'designation';
                        if coalesce(@ACCOUNTNUMBER, '') = ''
                            select @RECORDNAME = NAME from dbo.DESIGNATION with (nolock) where ID = @DESIGNATIONID;
                    end

                    select @ERRORMESSAGE = 
                        case 
                            when coalesce(@ACCOUNTNUMBER, '') = ''
                                then 'GL account/fund code is not defined for the ' + @RECORDNAME + ' ' +  @RECORDTYPE + '.'
                            when exists (select 1 from @DEBITSCREDITS where GLACCOUNTTYPEMAPPINGID is null)
                                then 'Payment method and revenue type of the revenue record does not map to a valid GL account type.'
                            --when coalesce(@CREDITACCOUNT, '') = '' or coalesce(@DEBITACCOUNT, '') = '' 

                            --    then 'GL account code is not defined for the ' + @CREDITACCOUNTTYPE + ' account type.'

                            when coalesce(@REVENUECATEGORYACCOUNT, '') = '' and @REVENUESPLITTYPECODE <> 1 
                                then 'GL account code is not defined for the ' + @REVENUECATEGORY + ' revenue category.'
                             else '' 
                        end;

                insert into @DATA
                    (ACCOUNTSTRING, TRANSACTIONTYPECODE, PROJECTCODE, ERRORMESSAGE, ACCOUNTID)
                select 
                    coalesce((replace((replace(@ACCOUNTNUMBER,'*',ACCOUNT)), '%', @REVENUECATEGORYACCOUNT)), ''),
                    TRANSACTIONTYPECODE,
                    @PROJECTCODE,
                    case @ERRORMESSAGE when '' then ERRORMESSAGE else @ERRORMESSAGE end,
                    null
                from
                    @DEBITSCREDITS;

                return;
            end