USP_SAVE_GIFTAIDGLDISTRIBUTION

            CREATE procedure dbo.USP_SAVE_GIFTAIDGLDISTRIBUTION
            (
                @REVENUESPLITGIFTAIDID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
                set nocount on;

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

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

                declare @ERRORMESSAGE nvarchar(255);

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

                declare @REVENUEID uniqueidentifier;
                select @REVENUEID = REVENUEID from dbo.REVENUESPLIT where ID = @REVENUESPLITGIFTAIDID;

                declare @ADJUSTMENTPOSTSTATUSCODE tinyint;
                set @ADJUSTMENTPOSTSTATUSCODE = 1;

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

                -- Generate GL distributions for the gift aid record.

                insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, POSTDATE, GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                                           BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, ERRORMESSAGE, MAPPEDVALUES, ACCOUNTID)
                select 
                    newid(),
                    GIFTAIDDISTRIBUTIONS.ACCOUNTSTRING,
                    GIFTAIDDISTRIBUTIONS.AMOUNT,
                    GIFTAIDDISTRIBUTIONS.PROJECT,
                    GIFTAIDDISTRIBUTIONS.REFERENCE, 
                    GIFTAIDDISTRIBUTIONS.TRANSACTIONTYPECODE,
                    GIFTAIDDISTRIBUTIONS.POSTDATE,
                    GIFTAIDDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    GIFTAIDDISTRIBUTIONS.BASECURRENCYID,
                    GIFTAIDDISTRIBUTIONS.TRANSACTIONAMOUNT,
                    GIFTAIDDISTRIBUTIONS.TRANSACTIONCURRENCYID,
                    GIFTAIDDISTRIBUTIONS.BASEEXCHANGERATEID,
                    GIFTAIDDISTRIBUTIONS.ORGANIZATIONAMOUNT,
                    GIFTAIDDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                    GIFTAIDDISTRIBUTIONS.ERRORMESSAGE,
                    GIFTAIDDISTRIBUTIONS.MAPPEDVALUES,
                    GIFTAIDDISTRIBUTIONS.ACCOUNTID
                from 
                    dbo.UFN_REVENUESPLITGIFTAID_GENERATEGLDISTRIBUTION(@REVENUESPLITGIFTAIDID) as GIFTAIDDISTRIBUTIONS
                where not exists
                    (select
                        1
                    from
                        dbo.JOURNALENTRY JE
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
                    where
                        LI.SOURCELINEITEMID = GIFTAIDDISTRIBUTIONS.REVENUESPLITID
                        and LI.POSTSTATUSCODE <> 1)


        --Only throw an error if the account ID is null because we want to allow the record to save but also trap the error

                -- when a holding account exists.

                select top 1 @ERRORMESSAGE = ERRORMESSAGE from @DISTRIBUTIONS where nullif(ERRORMESSAGE,'') is not null and ACCOUNTID is null;

                if nullif(@ERRORMESSAGE,'') is not null
                    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
                        R.REVENUEID
                        ,(select TRANSACTIONTYPECODE from dbo.REVENUE where ID = R.REVENUEID)
                        ,D.ERRORMESSAGE
                        ,convert(varchar(max),D.MAPPEDVALUES)
                        ,@CHANGEAGENTID
                        ,@CHANGEAGENTID
                        ,@CHANGEDATE
                        ,@CHANGEDATE
                    from @DISTRIBUTIONS D 
                    inner join dbo.REVENUESPLIT R on R.ID = @REVENUESPLITGIFTAIDID
                    where nullif(D.ERRORMESSAGE,'') is not null 
                        and D.ERRORMESSAGE not in (select ERRORMESSAGE from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = R.REVENUEID and DELETED = 0)


                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 @LINEITEMID uniqueidentifier;

                select top 1
                    @LINEITEMID = ID
                from
                    dbo.FINANCIALTRANSACTIONLINEITEM
                where
                    TYPECODE = 8 and
                    SOURCELINEITEMID = @REVENUESPLITGIFTAIDID and
                    DELETEDON is null and
                    FINANCIALTRANSACTIONID = @REVENUEID

                if @LINEITEMID is null 
                begin                

                    set @LINEITEMID = NEWID();
                    insert into dbo.FINANCIALTRANSACTIONLINEITEM (
                        ID
                        ,FINANCIALTRANSACTIONID
                        ,TRANSACTIONAMOUNT
                        ,VISIBLE
                        ,ADDEDBYID
                        ,CHANGEDBYID
                        ,DATEADDED
                        ,DATECHANGED
                        ,DESCRIPTION
                        ,SEQUENCE
                        ,TYPECODE
                        ,POSTDATE
                        ,POSTSTATUSCODE
                        ,BASEAMOUNT
                        ,ORGAMOUNT
                        ,SOURCELINEITEMID
                      )
                      select distinct
                        @LINEITEMID
                        ,@REVENUEID
                        ,isnull(tmp.TRANSACTIONAMOUNT, 0)
                        ,0
                        ,@CHANGEAGENTID
                        ,@CHANGEAGENTID
                        ,@CHANGEDATE
                        ,@CHANGEDATE
                        ,'GiftAid'
                        ,1
                        ,8
                        ,SOURCELI.POSTDATE
                        ,case SOURCELI.POSTSTATUSCODE when 3 then 3
                                    else 1
                                    end
                        ,tmp.AMOUNT
                        ,tmp.ORGANIZATIONAMOUNT
                        ,@REVENUESPLITGIFTAIDID
                      from @DISTRIBUTIONS as tmp 
                        inner join dbo.FINANCIALTRANSACTION as FT on @REVENUEID = FT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCELI on SOURCELI.FINANCIALTRANSACTIONID = FT.ID and SOURCELI.ID = @REVENUESPLITGIFTAIDID
                        left join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.FINANCIALTRANSACTIONID = FT.ID 
                            and FTLI.TYPECODE = 8 and FTLI.SOURCELINEITEMID = @REVENUESPLITGIFTAIDID
                        where FTLI.ID is null
                    end

            delete
            from
                dbo.JOURNALENTRY
            where
                FINANCIALTRANSACTIONLINEITEMID = @LINEITEMID

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

            insert into JOURNALENTRY_EXT
                (ID,DISTRIBUTIONTABLEID,PROJECT,ACCOUNT,JOURNAL,PRECALCORGANIZATIONEXCHANGERATEID,
                PRECALCBASEEXCHANGERATEID,GLPAYMENTMETHODREVENUETYPEMAPPINGID,TABLENAMECODE,OUTDATED,
                LOGICALREVENUEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED, REVENUESPLITGIFTAIDID)
            select
                D.GLTRANSACTIONID,NEWID(),coalesce(PROJECT, ''),GL.ACCOUNTNUMBER,@JOURNAL,ORGANIZATIONEXCHANGERATEID,
                BASEEXCHANGERATEID,GLPAYMENTMETHODREVENUETYPEMAPPINGID,7,0,
                @REVENUEID,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, @REVENUESPLITGIFTAIDID
            from 
                @DISTRIBUTIONS D
                inner join dbo.GLACCOUNT GL on GL.ID = D.ACCOUNTID