USP_REFUND_CREATEGLDISTRIBUTION_GROUPSALES

            CREATE procedure dbo.USP_REFUND_CREATEGLDISTRIBUTION_GROUPSALES
            (
                @CREDITPAYMENTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
            begin
                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                if @CHANGEDATE is null
                    set @CHANGEDATE = getdate();

                declare @PDACCOUNTSYSTEMID uniqueidentifier;
                declare @DEFAULTACCOUNT nvarchar(100);
                declare @DEFAULTACCOUNTID uniqueidentifier;

                select
                    @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID,
                    @DEFAULTACCOUNTID = GLACCOUNT.ID,
                    @DEFAULTACCOUNT = GLACCOUNT.ACCOUNTNUMBER 
                from
                    dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM() as PDACCOUNTSYSTEM
                left join
                    dbo.GLACCOUNT on GLACCOUNT.ID = PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID;

                declare @CREDITID uniqueidentifier
                select @CREDITID = CREDITID from dbo.CREDITPAYMENT where ID = @CREDITPAYMENTID

                declare @POSTDATE datetime;
                select @POSTDATE = POSTDATE
                from dbo.FINANCIALTRANSACTION where ID = @CREDITID;

            declare @DISTRIBUTIONS table(
                GLTRANSACTIONID uniqueidentifier,
                ACCOUNT nvarchar(100),
                AMOUNT money,
                TRANSACTIONTYPECODE tinyint,
                REFERENCE nvarchar(255),
                POSTDATE datetime,
                ACCOUNTID uniqueidentifier,
                CREDITPAYMENTID uniqueidentifier,
                ERRORMESSAGE nvarchar(255),
                MAPPEDVALUES xml
                ,FTLIID uniqueidentifier
            );

                        -- Generate GL distributions for the discount record.

            insert into @DISTRIBUTIONS(
                GLTRANSACTIONID,
                ACCOUNT,
                AMOUNT,
                TRANSACTIONTYPECODE,
                REFERENCE,
                POSTDATE,
                ACCOUNTID,
                CREDITPAYMENTID,
                FTLIID)
            select
                NEWID() as GLTRANSACTIONID
                ,case when JE.TRANSACTIONTYPECODE = 0 then
                    dbo.UFN_REFUND_GETCREDITACCOUNT_GROUPSALES(@CREDITPAYMENTID)
                else
                    A.ACCOUNTNUMBER
                end ACCOUNT
                ,REFUNDLI.TRANSACTIONAMOUNT as AMOUNT
                ,case when JE.TRANSACTIONTYPECODE = 0 then 1 else 0 end as TRANSACTIONTYPECODE
                ,'Refund-'+ JE.COMMENT as REFERENCE
                ,@POSTDATE as POSTDATE
                ,case when JE.TRANSACTIONTYPECODE = 0 then
                    null
                else
                    JE.GLACCOUNTID
                end ACCOUNTID
                ,@CREDITPAYMENTID as CREDITPAYMENTID
                ,REFUNDLI.ID as FTLIID
            from dbo.FINANCIALTRANSACTIONLINEITEM REFUNDLI 
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on REFUNDLI.SOURCELINEITEMID = LI.ID
            inner join dbo.REVENUESPLIT_EXT EXT on EXT.ID = LI.ID
            inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
            inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
            inner join dbo.GLACCOUNT A on A.ID = JE.GLACCOUNTID
            where REFUNDLI.FINANCIALTRANSACTIONID = @CREDITID
                and LI.TYPECODE != 1 and LI.DELETEDON is null
                and JEX.TABLENAMECODE = 1
                and EXT.APPLICATIONCODE = 10 --Order

                and EXT.TYPECODE = 19 -- Unearned revenue





            update @DISTRIBUTIONS set ACCOUNTID = (select GLACCOUNT.ID from dbo.GLACCOUNT where GLACCOUNT.ACCOUNTNUMBER = ACCOUNT and GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
            where ACCOUNTID is null;

            declare @ERRORMESSAGE nvarchar(255);
            if exists(select 1 from @DISTRIBUTIONS where ACCOUNTID is null)
                begin
                    set @ERRORMESSAGE = 'An account code has not been mapped for one or more payment methods.';

                    declare @MAPPEDVALUES xml;
                    set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 1 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 2, null, null, null, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)

                    if @DEFAULTACCOUNTID is null
                        raiserror(@ERRORMESSAGE, 13, 1);
                    else
                    begin
                        update @DISTRIBUTIONS set
                            ACCOUNT = @DEFAULTACCOUNT
                            ,ACCOUNTID = @DEFAULTACCOUNTID
                            ,ERRORMESSAGE = @ERRORMESSAGE
                            ,MAPPEDVALUES = convert(nvarchar(max), @MAPPEDVALUES)
                        where ACCOUNTID is null;

                        insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE], [MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                        select distinct
                            @CREDITID
                            ,103
                            ,D.ERRORMESSAGE
                            ,convert(nvarchar(max), D.MAPPEDVALUES)
                            ,@CHANGEAGENTID
                            ,@CHANGEAGENTID
                            ,@CHANGEDATE
                            ,@CHANGEDATE
                        from @DISTRIBUTIONS D
                        where nullif(D.ERRORMESSAGE, '') is not null and D.ERRORMESSAGE not in (select ERRORMESSAGE from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @CREDITID and DELETED = 0)
                    end
                end


            set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);
            if @ERRORMESSAGE <> ''
                raiserror(@ERRORMESSAGE, 13, 1);

            declare @JOURNAL nvarchar(50);
            set @JOURNAL = 'Blackbaud Enterprise';

            declare @CURRENCYID uniqueidentifier;
            select @CURRENCYID = ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;


            insert into dbo.JOURNALENTRY (
                ID
                ,FINANCIALTRANSACTIONLINEITEMID
                ,TRANSACTIONTYPECODE
                ,CLASSCODE
                ,SEQUENCE
                ,TRANSACTIONAMOUNT
                ,COMMENT
                ,POSTDATE
                ,GLACCOUNTID
                ,SUBLEDGERTYPECODE
                ,BASEAMOUNT
                ,ORGAMOUNT
                ,TYPECODE
                ,TRANSACTIONCURRENCYID
                ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select
                GLTRANSACTIONID
                ,FTLIID
                ,TRANSACTIONTYPECODE
                ,0
                ,row_number() over (partition by FTLIID order by AMOUNT)
                ,AMOUNT
                ,REFERENCE
                ,POSTDATE
                ,ACCOUNTID
                ,TRANSACTIONTYPECODE
                ,AMOUNT
                ,AMOUNT
                ,0
                ,@CURRENCYID
                ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
            from @DISTRIBUTIONS;

            insert into dbo.JOURNALENTRY_EXT(
                ID
                ,TABLENAMECODE
                ,OUTDATED
                ,CREDITITEMID
                ,CREDITPAYMENTID
                ,JOURNAL
                ,ACCOUNT
                ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select
                GLTRANSACTIONID
                ,6
                ,0
                ,FTLIID
                ,CREDITPAYMENTID
                ,@JOURNAL
                ,ACCOUNT
                ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
            from @DISTRIBUTIONS;
            end