USP_REVENUE_UPDATEAUCTIONPURCHASEGLDISTRIBUTION

            CREATE procedure dbo.USP_REVENUE_UPDATEAUCTIONPURCHASEGLDISTRIBUTION
            (
                @REVENUEID uniqueidentifier,
                @OLDAUCTIONPURCHASES xml,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @ADJUSTMENTDATE datetime = null, 
                @ADJUSTMENTPOSTDATE datetime = null, 
                @ADJUSTMENTREASON nvarchar(300) = null,  
                @ADJUSTMENTREASONCODEID uniqueidentifier = null,
                @ADJUSTMENTPOSTSTATUSCODE tinyint = 1
            )
            as
            begin

                set nocount on;

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

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

                declare @PDACCOUNTSYSTEMID uniqueidentifier;
                if @PDACCOUNTSYSTEMID is null
                    select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID
                    from dbo.REVENUE inner join dbo.PDACCOUNTSYSTEMFORREVENUE on REVENUE.ID = PDACCOUNTSYSTEMFORREVENUE.ID
                    where REVENUE.ID = @REVENUEID

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

                if @ADJUSTMENTDATE is null
                    select @ADJUSTMENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE)

                declare @PAYMENTPOSTSTATUSCODE tinyint;
                select @PAYMENTPOSTSTATUSCODE = case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end
                        from dbo.REVENUE left join dbo.REVENUEPOSTED on REVENUE.ID = REVENUEPOSTED.ID where REVENUE.ID = @REVENUEID

                declare @DISTRIBUTIONS table(
                    GLTRANSACTIONID uniqueidentifier,
                    ACCOUNT nvarchar(100),
                    AMOUNT money,
                    PROJECT nvarchar(100),
                    REFERENCE nvarchar(255),
                    TRANSACTIONTYPECODE tinyint,
                    POSTDATE datetime,
                    GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
                    ACCOUNTID uniqueidentifier,
                    REVENUEID uniqueidentifier,
                    REVENUESPLITTYPECODE tinyint,
                    POSTSTATUSCODE tinyint,
                    AUCTIONITEMPOSTSTATUSCODE tinyint,
                    REVENUEPURCHASEID uniqueidentifier,
                    BASECURRENCYID uniqueidentifier,
                    TRANSACTIONCURRENCYID uniqueidentifier,
                    BASEEXCHANGERATEID uniqueidentifier,
                    ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                    TRANSACTIONAMOUNT money,
                    ORGANIZATIONAMOUNT money,
                    ERRORMESSAGE nvarchar(max),
                    MAPPEDVALUES xml,
                    SYSTEMDISTRIBUTION bit default 0,
                    REVENUESPLITID uniqueidentifier
                );

                insert into @DISTRIBUTIONS (ACCOUNTID, ERRORMESSAGE, MAPPEDVALUES)
                select ACCOUNTID, ERRORMESSAGE, MAPPEDVALUES 
                from dbo.UFN_REVENUE_GENERATEAUCTIONPURCHASEGLDISTRIBUTION_2(@REVENUEID,null) 
                where nullif(ERRORMESSAGE,'') is not null

                select top 1 @ERRORMESSAGE = ERRORMESSAGE from @DISTRIBUTIONS where ACCOUNTID is null;
                if nullif(@ERRORMESSAGE,'') is not null
                    raiserror('%s', 13, 1, @ERRORMESSAGE);
                else
                    insert into dbo.GLACCOUNTMAPPINGERROR (
                        [TRANSACTIONID]
                        ,[TRANSACTIONTYPECODE]
                        ,[ERRORMESSAGE]
                        ,[MAPPEDVALUES]
                        ,[ADDEDBYID]
                        ,[CHANGEDBYID]
                        ,[DATEADDED]
                        ,[DATECHANGED])
                    select distinct
                        @REVENUEID
                        ,(select TRANSACTIONTYPECODE from dbo.REVENUE where ID = @REVENUEID)
                        ,D.ERRORMESSAGE
                        ,convert(varchar(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 = @REVENUEID and DELETED = 0)

                delete from @DISTRIBUTIONS

                declare @AFFECTEDAUCTIONITEMS table
                (
                    AUCTIONITEMID uniqueidentifier,
                    REVENUEAUCTIONDONATIONID uniqueidentifier,
                    DONOTPOST bit
                )

                insert into @AFFECTEDAUCTIONITEMS
                select
                    AUCTIONITEMID,REVENUEAUCTIONDONATIONID,DONOTPOST
                from dbo.UFN_REVENUE_GETCHANGEDORDELETEDAUCTIONPURCHASES(@REVENUEID, @OLDAUCTIONPURCHASES)

                --WI 158114 BBNT\RyanDow (Ryan Dowacter)

                --We should run this code to reverse/adjust auction purchases even if the auction purchase stream isn't that one that got changed

                --if (select count(AUCTIONITEMID) from @AFFECTEDAUCTIONITEMS) > 0

                --begin


                    --Get the affected revenue (payments that paid towards items that were changed in the current payment)

                    declare @AFFECTEDREVENUE table
                    (
                        REVENUEPURCHASEID uniqueidentifier,
                        POSTSTATUSCODE tinyint
                    )

                    insert into @AFFECTEDREVENUE(REVENUEPURCHASEID,POSTSTATUSCODE)
                    (    
                        select @REVENUEID,@PAYMENTPOSTSTATUSCODE
                            union all
                        select distinct
                            REVENUE.ID,
                            case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end
                        from @AFFECTEDAUCTIONITEMS AFFECTEDAUCTIONITEMS
                        inner join dbo.AUCTIONITEMREVENUEPURCHASE on AFFECTEDAUCTIONITEMS.AUCTIONITEMID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
                        inner join dbo.REVENUE on REVENUE.ID = AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID
                        left join dbo.REVENUEPOSTED on REVENUE.ID = REVENUEPOSTED.ID
                        where AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID <> @REVENUEID
                    )

                    declare @PAYMENTCURSOR_REVENUEPURCHASEID uniqueidentifier,@PAYMENTCURSOR_POSTSTATUSCODE tinyint;
                    declare PAYMENTCURSOR cursor local fast_forward
                    for select REVENUEPURCHASEID,POSTSTATUSCODE from @AFFECTEDREVENUE
                    open PAYMENTCURSOR
                    fetch next from PAYMENTCURSOR into @PAYMENTCURSOR_REVENUEPURCHASEID, @PAYMENTCURSOR_POSTSTATUSCODE

                    while @@FETCH_STATUS = 0
                    begin
                        delete dbo.JOURNALENTRY where ID in (    
                            select  JE.ID
                            from dbo.JOURNALENTRY JE     
                            inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
                            where JEX.REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
                                and LI.POSTSTATUSCODE != 2);

                        if exists(select 1 
                            from dbo.JOURNALENTRY JE     
                            inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
                            where JEX.REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
                                and LI.POSTSTATUSCODE = 2)
                        begin
                            exec dbo.USP_SAVE_AUCTIONPURCHASEADJUSTMENT 
                                @REVENUEID = @PAYMENTCURSOR_REVENUEPURCHASEID,
                                @CHANGEAGENTID = @CHANGEAGENTID,
                                @CHANGEDATE = @CHANGEDATE,
                                @DATE = @ADJUSTMENTDATE,
                                @POSTDATE = @ADJUSTMENTPOSTDATE,  
                                @ADJUSTMENTREASON = @ADJUSTMENTREASON,  
                                @ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID,
                                @POSTSTATUSCODE = @ADJUSTMENTPOSTSTATUSCODE
                        end    

                        if @PAYMENTCURSOR_POSTSTATUSCODE <> 2
                        begin

                             -- Generate GL distributions for the auction purchase record.

                            insert into @DISTRIBUTIONS(GLTRANSACTIONID,ACCOUNT,AMOUNT,PROJECT,REFERENCE,TRANSACTIONTYPECODE,POSTDATE,GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNTID,REVENUEID,REVENUESPLITTYPECODE,POSTSTATUSCODE,AUCTIONITEMPOSTSTATUSCODE,REVENUEPURCHASEID,BASECURRENCYID,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,ORGANIZATIONAMOUNT,ERRORMESSAGE,MAPPEDVALUES,REVENUESPLITID)
                            select 
                                newid(),
                                DISTRIBUTIONS.ACCOUNTSTRING,
                                DISTRIBUTIONS.AMOUNT,
                                DISTRIBUTIONS.PROJECT,
                                DISTRIBUTIONS.REFERENCE, 
                                DISTRIBUTIONS.TRANSACTIONTYPECODE,
                                DISTRIBUTIONS.POSTDATE,
                                DISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                                DISTRIBUTIONS.ACCOUNTID,
                                DISTRIBUTIONS.REVENUEID,
                                DISTRIBUTIONS.REVENUESPLITTYPECODE,
                                1, --DISTRIBUTIONS.POSTSTATUSCODE,

                                DISTRIBUTIONS.AUCTIONITEMPOSTSTATUSCODE,
                                DISTRIBUTIONS.REVENUEPURCHASEID,
                                DISTRIBUTIONS.BASECURRENCYID,
                                DISTRIBUTIONS.TRANSACTIONCURRENCYID,
                                DISTRIBUTIONS.BASEEXCHANGERATEID,
                                DISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                                DISTRIBUTIONS.TRANSACTIONAMOUNT,
                                DISTRIBUTIONS.ORGANIZATIONAMOUNT,
                                DISTRIBUTIONS.ERRORMESSAGE,
                                DISTRIBUTIONS.MAPPEDVALUES,
                                DISTRIBUTIONS.REVENUESPLITID
                            from 
                                dbo.UFN_REVENUE_GENERATEAUCTIONPURCHASEGLDISTRIBUTION_2(@PAYMENTCURSOR_REVENUEPURCHASEID,null) as DISTRIBUTIONS
                            where not exists(select 1 from @DISTRIBUTIONS CURRENTDISTRIBUTIONS where CURRENTDISTRIBUTIONS.REVENUEPURCHASEID = DISTRIBUTIONS.REVENUEPURCHASEID and CURRENTDISTRIBUTIONS.REVENUEID = DISTRIBUTIONS.REVENUEID);

                            if @@rowcount > 0      
                                set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE((select top 1 POSTDATE from @DISTRIBUTIONS));

                            if @ERRORMESSAGE <> ''
                                raiserror(@ERRORMESSAGE, 13, 1);        


                            declare @Cache table (HashCode uniqueidentifier, ErrorMessage nvarchar(max), GLAccountID uniqueidentifier, TransactionTypeCode tinyint, ErrorNumber tinyint, MappedValues nvarchar(max), PDAccountSystemID uniqueidentifier)
                            insert into @Cache(HashCode, ErrorMessage, GLAccountID, TransactionTypeCode, ErrorNumber, MappedValues, PDAccountSystemID)
                            select
                                v.MD5HASHCODE
                            ,d.ERRORMESSAGE
                            ,d.ACCOUNTID
                            ,d.TRANSACTIONTYPECODE
                            ,v.ERRORCODE
                            ,v.MAPPEDVALUES
                            ,v.PDACCOUNTSYSTEMID
                            from @DISTRIBUTIONS d
                            cross apply dbo.UFN_PDACCOUNTLOOKUPCACHE_PARSE_MAPPEDVALUES(MAPPEDVALUES) v
                            where v.MD5HASHCODE is not null

                            insert into dbo.PDACCOUNTLOOKUPCACHE (MD5HASHCODE, ERRORMESSAGE, GLACCOUNTID, TRANSACTIONTYPECODE, ERRORNUMBER, MAPPEDVALUES, PDACCOUNTSYSTEMID)
                            select HashCode, ErrorMessage, GLAccountID, TransactionTypeCode, ErrorNumber, MappedValues, PDAccountSystemID
                            from @Cache tt
                            where not exists (select MD5HASHCODE from dbo.PDACCOUNTLOOKUPCACHE with (NOLOCK) where MD5HASHCODE = tt.HashCode and TRANSACTIONTYPECODE = tt.TransactionTypeCode and MAPPEDVALUES = tt.MappedValues)


                            declare @ORGDECIMALDIGITS tinyint;
                            declare @ORGROUNDINGTYPECODE tinyint;

                            select 
                                @ORGDECIMALDIGITS = ORGCURRENCY.DECIMALDIGITS,
                                @ORGROUNDINGTYPECODE = ORGCURRENCY.ROUNDINGTYPECODE
                            --from @DISTRIBUTIONS DISTRIBUTION

                            --inner join dbo.CURRENCY ORGCURRENCY on ORGCURRENCY.ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()

                            from dbo.CURRENCY ORGCURRENCY
                            where ORGCURRENCY.ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()

                            --Change to FTM.  First change is just do a single insert into JournalEntry                 

                            insert into dbo.JOURNALENTRY (ID, FINANCIALTRANSACTIONLINEITEMID, TRANSACTIONTYPECODE, BASEAMOUNT, COMMENT, 
                                POSTDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, GLACCOUNTID, ORGAMOUNT, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, TYPECODE, SUBLEDGERTYPECODE)        
                            select GLTRANSACTIONID, REVENUESPLITID, TRANSACTIONTYPECODE, AMOUNT, REFERENCE,
                                POSTDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, ACCOUNTID, ORGANIZATIONAMOUNT, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, case when SYSTEMDISTRIBUTION = 1 then 1 else 0 end, TRANSACTIONTYPECODE
                            from @DISTRIBUTIONS
                            where 
                                REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
                            and
                                (AMOUNT != 0 or TRANSACTIONAMOUNT != 0 or 
                                (dbo.UFN_AUCTIONPACKAGE_CURRENCY_ROUND(ORGANIZATIONAMOUNT, @ORGDECIMALDIGITS, @ORGROUNDINGTYPECODE) != 0));

                            insert into dbo.JOURNALENTRY_EXT (ID, DISTRIBUTIONTABLEID, TABLENAMECODE, PROJECT, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, PRECALCPOSTSTATUSCODE, PRECALCORGANIZATIONEXCHANGERATEID,
                                PRECALCBASEEXCHANGERATEID,  ACCOUNT, GLPAYMENTMETHODREVENUETYPEMAPPINGID, REVENUEPURCHASEID)
                            select GLTRANSACTIONID,GLTRANSACTIONID,2,PROJECT, @JOURNAL, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, POSTSTATUSCODE, ORGANIZATIONEXCHANGERATEID,
                                BASEEXCHANGERATEID,  ACCOUNT, GLPAYMENTMETHODREVENUETYPEMAPPINGID, @PAYMENTCURSOR_REVENUEPURCHASEID
                            from @DISTRIBUTIONS
                            where 
                                REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
                            and
                                (AMOUNT != 0 or TRANSACTIONAMOUNT != 0 or 
                                (dbo.UFN_AUCTIONPACKAGE_CURRENCY_ROUND(ORGANIZATIONAMOUNT, @ORGDECIMALDIGITS, @ORGROUNDINGTYPECODE) != 0));

                            declare @ADJUSTMENTID uniqueidentifier

                            select top 1
                                @ADJUSTMENTID = FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
                            from
                                dbo.FINANCIALTRANSACTIONLINEITEM LI
                            where
                                LI.FINANCIALTRANSACTIONID = @REVENUEID
                                and LI.POSTSTATUSCODE = 1
                                and LI.TYPECODE = 1
                                and LI.DELETEDON is null

                        if @ADJUSTMENTID is not null
                        begin
                                update LI set
                                    FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID,
                                    POSTDATE = @ADJUSTMENTPOSTDATE
                                from
                                    @DISTRIBUTIONS D
                                    inner join dbo.JOURNALENTRY JE on JE.ID = D.GLTRANSACTIONID
                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
                                where
                                    LI.FINANCIALTRANSACTIONID = @REVENUEID
                                    and LI.POSTSTATUSCODE = 1
                                    and LI.TYPECODE <> 1
                                    and LI.DELETEDON is null
                         end

                        end

                        fetch next from PAYMENTCURSOR into @PAYMENTCURSOR_REVENUEPURCHASEID, @PAYMENTCURSOR_POSTSTATUSCODE
                    end

                    close PAYMENTCURSOR;
                    deallocate PAYMENTCURSOR;

                    exec dbo.USP_SAVE_AUCTIONPURCHASEREALIZEDGLDISTRIBUTION 
                        @REVENUEID=@REVENUEID, 
                        @PDACCOUNTSYSTEMID=@PDACCOUNTSYSTEMID, 
                        @CHANGEAGENTID=@CHANGEAGENTID, 
                        @CURRENTDATE=@CHANGEDATE, 
                        @REVENUEIDONLY=0;

                --end

            end