USP_SAVE_WRITEOFFGLDISTRIBUTION_PROCESS

            CREATE procedure [dbo].[USP_SAVE_WRITEOFFGLDISTRIBUTION_PROCESS]
            (
                @REVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @ERRORMESSAGE nvarchar(max) = null output
            )
            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_PROCESS_CUSTOMIZE @REVENUEID,@CHANGEAGENTID,@CHANGEDATE,@ERRORMESSAGE output

                if @CUSTOMIZED = 0
                begin
                    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                        
                    );

                    -- 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
                    )
                    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
                    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 <> ''
                        return 1;
                    else
                    begin
                        --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);

                        set @ERRORMESSAGE = null;
                    end

            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)

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

                if @ERRORMESSAGE <> ''
                        return 1;

                    -- Insert rows in GLTRANSACTION table

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

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