USP_SAVE_GIFTFEEGLDISTRIBUTION

            CREATE procedure [dbo].[USP_SAVE_GIFTFEEGLDISTRIBUTION]
            (
                @REVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @REVENUESPLITGIFTFEEID uniqueidentifier = null
            )
            as
                set nocount on;

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

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

                declare @CUSTOMIZED integer = 0;
                exec  @CUSTOMIZED = dbo.USP_SAVE_GIFTFEEGLDISTRIBUTION_CUSTOMIZE @REVENUEID,@CHANGEAGENTID,@CHANGEDATE,@REVENUESPLITGIFTFEEID

                if @CUSTOMIZED = 0
                begin
                    declare @JOURNAL nvarchar(50);
                    set @JOURNAL = 'Blackbaud Enterprise';

                    declare @DISTRIBUTIONS table(
                        REVENUESPLITGIFTFEEID uniqueidentifier,
                        GLTRANSACTIONID uniqueidentifier,
                        ACCOUNT nvarchar(100),
                        AMOUNT money,
                        REFERENCE nvarchar(255),
                        TRANSACTIONTYPECODE tinyint,
                        POSTDATE datetime,
                        GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
                        ACCOUNTID uniqueidentifier,
                        BASECURRENCYID uniqueidentifier,
                        TRANSACTIONAMOUNT money,
                        TRANSACTIONCURRENCYID uniqueidentifier,
                        BASEEXCHANGERATEID uniqueidentifier,
                        ORGANIZATIONAMOUNT money,
                        ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                        ERRORMESSAGE nvarchar(max),
                        MAPPEDVALUES xml,
                        PAYMENTMETHODCODE tinyint
                    );

                    -- Generate GL distributions for the stock detail record.

                    insert into @DISTRIBUTIONS(GLTRANSACTIONID, REVENUESPLITGIFTFEEID, ACCOUNT, AMOUNT, REFERENCE, TRANSACTIONTYPECODE, POSTDATE, GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNTID, BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, ERRORMESSAGE, MAPPEDVALUES, PAYMENTMETHODCODE)
                    select 
                        newid(),
                        GIFTFEEDISTRIBUTIONS.REVENUESPLITGIFTFEEID,
                        GIFTFEEDISTRIBUTIONS.ACCOUNTSTRING,
                        GIFTFEEDISTRIBUTIONS.AMOUNT,
                        GIFTFEEDISTRIBUTIONS.REFERENCE, 
                        GIFTFEEDISTRIBUTIONS.TRANSACTIONTYPECODE,
                        GIFTFEEDISTRIBUTIONS.POSTDATE,
                        GIFTFEEDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        GIFTFEEDISTRIBUTIONS.ACCOUNTID,
                        GIFTFEEDISTRIBUTIONS.BASECURRENCYID,
                        GIFTFEEDISTRIBUTIONS.TRANSACTIONAMOUNT,
                        GIFTFEEDISTRIBUTIONS.TRANSACTIONCURRENCYID,
                        GIFTFEEDISTRIBUTIONS.BASEEXCHANGERATEID,
                        GIFTFEEDISTRIBUTIONS.ORGANIZATIONAMOUNT,
                        GIFTFEEDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                        GIFTFEEDISTRIBUTIONS.ERRORMESSAGE,
                        GIFTFEEDISTRIBUTIONS.MAPPEDVALUES,
                        coalesce(RPM.PAYMENTMETHODCODE, 1)     
                    from 
                        dbo.UFN_REVENUE_GENERATEGIFTFEEGLDISTRIBUTION(@REVENUEID) as GIFTFEEDISTRIBUTIONS
                        left join
                        REVENUEPAYMENTMETHOD as RPM on RPM.REVENUEID = @REVENUEID
                    where not exists
                        (select ID from dbo.GIFTFEEGLDISTRIBUTION where GIFTFEEGLDISTRIBUTION.REVENUESPLITGIFTFEEID = GIFTFEEDISTRIBUTIONS.REVENUESPLITGIFTFEEID and GIFTFEEGLDISTRIBUTION.OUTDATED = 0)
                        and GIFTFEEDISTRIBUTIONS.POSTSTATUSCODE > 0
                        and (@REVENUESPLITGIFTFEEID is null or GIFTFEEDISTRIBUTIONS.REVENUESPLITGIFTFEEID = @REVENUESPLITGIFTFEEID);

                    declare @ERRORMESSAGE nvarchar(max);

                    select top 1 @ERRORMESSAGE = ERRORMESSAGE from @DISTRIBUTIONS where ACCOUNTID is null

                    if @ERRORMESSAGE <> ''
                        raiserror('%s', 13, 1, @ERRORMESSAGE);
                    else
                        --Trap the error when a holding account exists so we can show it to the user later.

                        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)

                    if (select count(*) from @DISTRIBUTIONS) > 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 @ISPOSTED as bit = 0;
        select @ISPOSTED = case POSTSTATUSCODE when 2 then 1 else 0 end from dbo.FINANCIALTRANSACTION where ID = @REVENUEID;

                -- Insert rows in GLTRANSACTION table

                insert into dbo.GLTRANSACTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, REFERENCE, POSTDATE, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, GLACCOUNTID, BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, PAYMENTMETHODCODE)
                select
                    GLTRANSACTIONID,
                    TRANSACTIONTYPECODE,
                    ACCOUNT,
                    AMOUNT,
                    REFERENCE,
                    POSTDATE,
                    @JOURNAL,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CHANGEDATE,
                    @CHANGEDATE,
                    ACCOUNTID,
                    BASECURRENCYID, 
                    TRANSACTIONAMOUNT, 
                    TRANSACTIONCURRENCYID, 
                    BASEEXCHANGERATEID, 
                    ORGANIZATIONAMOUNT, 
                    ORGANIZATIONEXCHANGERATEID,
                    PAYMENTMETHODCODE
                from 
                    @DISTRIBUTIONS;

                    insert into dbo.GIFTFEEGLDISTRIBUTION
                        (ID, REVENUESPLITGIFTFEEID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, GLTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID)
                    select
                        newid(),
                        REVENUESPLITGIFTFEEID,
                        @REVENUEID,
                        GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        REFERENCE, 
                        AMOUNT,
                        ACCOUNT,
                        TRANSACTIONTYPECODE,
                        GLTRANSACTIONID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE, 
                        BASECURRENCYID, 
                        TRANSACTIONAMOUNT, 
                        TRANSACTIONCURRENCYID, 
                        BASEEXCHANGERATEID, 
                        ORGANIZATIONAMOUNT, 
                        ORGANIZATIONEXCHANGERATEID
                    from 
                        @DISTRIBUTIONS;


                if @ISPOSTED = 1
                begin
                update LI set
                    TRANSACTIONAMOUNT = T.[TRANAMOUNT]
                    ,BASEAMOUNT = T.[BASEAMOUNT]
                    ,ORGAMOUNT =  T.[ORGAMOUNT]
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CHANGEDATE
                from (select SUM(D.TRANSACTIONAMOUNT)/2 [TRANAMOUNT]
                        ,SUM(D.AMOUNT)/2 [BASEAMOUNT]
                      ,SUM(D.ORGANIZATIONAMOUNT)/2 [ORGAMOUNT]
                        ,LI.ID
                    from dbo.FINANCIALTRANSACTIONLINEITEM LI
                    inner join dbo.JOURNALENTRY_EXT JEX on LI.SOURCELINEITEMID = JEX.REVENUESPLITGIFTFEEID
                    inner join dbo.JOURNALENTRY JE on JE.ID = JEX.ID
                    inner join @DISTRIBUTIONS D on D.GLTRANSACTIONID = JEX.ID
                    where JE.FINANCIALTRANSACTIONLINEITEMID is null and LI.DELETEDON is null and LI.TYPECODE = 7
                    group by LI.ID) T
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = T.ID;

                update JE set
                    FINANCIALTRANSACTIONLINEITEMID = LI.ID
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CHANGEDATE
                from dbo.JOURNALENTRY JE
                inner join @DISTRIBUTIONS D on D.GLTRANSACTIONID = JE.ID
                inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.SOURCELINEITEMID = JEX.REVENUESPLITGIFTFEEID
                where JE.FINANCIALTRANSACTIONLINEITEMID is null and LI.DELETEDON is null and LI.TYPECODE = 7;
              end 
      else 
        begin
                update LI set
                    TRANSACTIONAMOUNT = T.[TRANAMOUNT]
                    ,BASEAMOUNT = T.[BASEAMOUNT]
                    ,ORGAMOUNT =  T.[ORGAMOUNT]
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CHANGEDATE
                from (select SUM(D.TRANSACTIONAMOUNT)/2 [TRANAMOUNT]
                        ,SUM(D.AMOUNT)/2 [BASEAMOUNT]
                        ,SUM(D.ORGANIZATIONAMOUNT)/2 [ORGAMOUNT]
                        ,LI.ID
                    from dbo.FINANCIALTRANSACTIONLINEITEM LI
                    inner join dbo.JOURNALENTRY_EXT JEX on LI.SOURCELINEITEMID = JEX.REVENUESPLITGIFTFEEID
                    inner join dbo.JOURNALENTRY JE on JE.ID = JEX.ID
                    inner join @DISTRIBUTIONS D on D.GLTRANSACTIONID = JEX.ID
                    where LI.DELETEDON is null and LI.TYPECODE = 7
                    group by LI.ID) T
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = T.ID;

        end
    end