USP_REFUND_CREATEDISCOUNTGLDISTRIBUTION

            CREATE procedure dbo.USP_REFUND_CREATEDISCOUNTGLDISTRIBUTION(
                @CREDITID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
            begin
                set nocount on;

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

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

                --first, go get the distributions that we need to reverse...

                declare @DISTRIBUTIONS table
                (
                    REVENUESPLITID uniqueidentifier,
                    REVENUEID uniqueidentifier,
                    GLTRANSACTIONID uniqueidentifier,
                    ACCOUNT nvarchar(100),
                    AMOUNT money,
                    PROJECT nvarchar(100),
                    TRANSACTIONTYPECODE tinyint,
                    REFERENCE nvarchar(255),
                    POSTDATE datetime,
                    GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
                    ACCOUNTID uniqueidentifier,
                    CREDITITEMID uniqueidentifier,
                    DISCOUNTCREDITITEMID uniqueidentifier,
                    ERRORMESSAGE nvarchar(255),
                    MAPPEDVALUES xml
                );

                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 outer join dbo.GLACCOUNT on GLACCOUNT.ID = PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID;

                -- Generate GL distributions for the discount record.

                insert into @DISTRIBUTIONS(GLTRANSACTIONID, REVENUESPLITID, REVENUEID, ACCOUNT, AMOUNT, TRANSACTIONTYPECODE, REFERENCE, POSTDATE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, ACCOUNTID, CREDITITEMID, DISCOUNTCREDITITEMID)
                select
                    newid(),
                    JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID,
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                    case when JOURNALENTRY.TRANSACTIONTYPECODE = 0 then -- COULD BE OPTIMIZED HERE

                            (
                                select 
                                    coalesce(GLA.ACCOUNTNUMBER, '')
                                from
                                    JOURNALENTRY JE
                                left join 
                                    dbo.JOURNALENTRY_EXT JEXT on JEXT.ID = JE.ID
                                left join 
                                    dbo.GLACCOUNT GLA on JE.GLACCOUNTID = GLA.ID
                                where 
                                    JEXT.CREDITITEMID = SPLITS.DISCOUNTCREDITITEMID 
                                    and JE.TRANSACTIONTYPECODE = 0
                            )
                        else
                            isnull(GLACCOUNT.ACCOUNTNUMBER, JOURNALENTRY_EXT.ACCOUNT)
                        end,
                    SPLITS.AMOUNT,
                    case when JOURNALENTRY.TRANSACTIONTYPECODE = 0 then 1 else 0 end,
                    SPLITS.REFERENCE,
                    @POSTDATE,
                    isnull(JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID,'00000000-0000-0000-0000-000000000000'),
                    case when JOURNALENTRY.TRANSACTIONTYPECODE = 0 then
                        null
                    else
                        JOURNALENTRY.GLACCOUNTID
                        end,
                    SPLITS.CREDITITEMID,
                    SPLITS.DISCOUNTCREDITITEMID
                from 
                    dbo.JOURNALENTRY
                left outer join 
                    dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID
                left outer join
                    dbo.JOURNALENTRY_EXT on JOURNALENTRY_EXT.ID = JOURNALENTRY.ID
                inner join 
                    dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
                inner join 
                    dbo.UFN_REFUND_GETPRORATEDDISCOUNTSPLITS(@CREDITID) as SPLITS on SPLITS.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID

                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
                    ,CREDITITEMID
                    ,TRANSACTIONTYPECODE
                    ,0
                    ,row_number() over (partition by CREDITITEMID 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
                    ,DISCOUNTCREDITITEMID
                    ,JOURNAL
                    ,ACCOUNT
                    ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    GLTRANSACTIONID
                    ,6
                    ,0
                    ,CREDITITEMID
                    ,DISCOUNTCREDITITEMID
                    ,@JOURNAL
                    ,ACCOUNT
                    ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                from @DISTRIBUTIONS;
            end