USP_SAVE_WRITEOFFGLDISTRIBUTION

            CREATE procedure [dbo].[USP_SAVE_WRITEOFFGLDISTRIBUTION]
            (
                @REVENUEID 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 @CUSTOMIZED integer = 0;
                exec  @CUSTOMIZED = dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION_CUSTOMIZE @REVENUEID,@CHANGEAGENTID,@CHANGEDATE

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

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

                    -- Generate GL distributions for the write-off record.

                    insert into @DISTRIBUTIONS(
                        GLTRANSACTIONID, 
                        WRITEOFFID, 
                        ACCOUNT, 
                        AMOUNT, 
                        PROJECT, 
                        REFERENCE, 
                        TRANSACTIONTYPECODE, 
                        POSTDATE, 
                        GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        ACCOUNTID, 
                        TRANSACTIONAMOUNT, 
                        ORGANIZATIONAMOUNT, 
                        TRANSACTIONCURRENCYID, 
                        BASECURRENCYID, 
                        BASEEXCHANGERATEID, 
                        ORGANIZATIONEXCHANGERATEID,
                        ERRORMESSAGE,
                        MAPPEDVALUES
                        ,WRITEOFFLINEITEMID
                    )
                    select 
                        newid(),
                        WRITEOFFDISTRIBUTIONS.WRITEOFFID,
                        WRITEOFFDISTRIBUTIONS.ACCOUNTSTRING,
                        WRITEOFFDISTRIBUTIONS.AMOUNT,
                        WRITEOFFDISTRIBUTIONS.PROJECT,
                        WRITEOFFDISTRIBUTIONS.REFERENCE, 
                        WRITEOFFDISTRIBUTIONS.TRANSACTIONTYPECODE,
                        WRITEOFFDISTRIBUTIONS.POSTDATE,
                        WRITEOFFDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        WRITEOFFDISTRIBUTIONS.ACCOUNTID, 
                        WRITEOFFDISTRIBUTIONS.TRANSACTIONAMOUNT, 
                        WRITEOFFDISTRIBUTIONS.ORGANIZATIONAMOUNT, 
                        WRITEOFFDISTRIBUTIONS.TRANSACTIONCURRENCYID, 
                        WRITEOFFDISTRIBUTIONS.BASECURRENCYID, 
                        WRITEOFFDISTRIBUTIONS.BASEEXCHANGERATEID, 
                        WRITEOFFDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                        WRITEOFFDISTRIBUTIONS.ERRORMESSAGE,
                        WRITEOFFDISTRIBUTIONS.MAPPEDVALUES
                        ,WRITEOFFDISTRIBUTIONS.REVENUESPLITID
                    from 
                        dbo.UFN_REVENUE_GENERATEWRITEOFFGLDISTRIBUTION(@REVENUEID) as WRITEOFFDISTRIBUTIONS
                    where not exists
                        (select ID from dbo.WRITEOFFGLDISTRIBUTION where WRITEOFFGLDISTRIBUTION.WRITEOFFID = WRITEOFFDISTRIBUTIONS.WRITEOFFID and WRITEOFFGLDISTRIBUTION.OUTDATED = 0)
                        and WRITEOFFDISTRIBUTIONS.POSTSTATUSCODE = 1;

                    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)

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

                insert into dbo.JOURNALENTRY_EXT (ID, DISTRIBUTIONTABLEID, TABLENAMECODE, PROJECT, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, PRECALCORGANIZATIONEXCHANGERATEID,
                    PRECALCBASEEXCHANGERATEID,  ACCOUNT, GLPAYMENTMETHODREVENUETYPEMAPPINGID, WRITEOFFID, LOGICALREVENUEID)
                select GLTRANSACTIONID,GLTRANSACTIONID,12,PROJECT, @JOURNAL, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, ORGANIZATIONEXCHANGERATEID,
                    BASEEXCHANGERATEID,  ACCOUNT, GLPAYMENTMETHODREVENUETYPEMAPPINGID, WRITEOFFID, @REVENUEID
                from @DISTRIBUTIONS        
            end