USP_SAVE_PLEDGEWRITEOFFGLDISTRIBUTION

CREATE procedure [dbo].[USP_SAVE_PLEDGEWRITEOFFGLDISTRIBUTION]
    (
      @REVENUEID uniqueidentifier,
      @WRITEOFFIDTABLE UDT_GENERICID readonly,
      @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_PLEDGEWRITEOFFGLDISTRIBUTION_CUSTOMIZE @REVENUEID,@WRITEOFFIDTABLE,@CHANGEAGENTID,@CHANGEDATE

        if @CUSTOMIZED = 0
        begin
            declare @TRANSACTIONTYPECODE tinyint 
            select @TRANSACTIONTYPECODE = TYPECODE 
            from dbo.FINANCIALTRANSACTION 
            where ID = @REVENUEID

            if @TRANSACTIONTYPECODE not in (1, 15)
                exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CHANGEDATE;               
            else
            begin
                declare @ERRORMESSAGE nvarchar(max);
                declare @JOURNAL nvarchar(50);
                set @JOURNAL = 'Blackbaud Enterprise';

                declare @DISTRIBUTIONS table(
                    WRITEOFFID uniqueidentifier,
                    GLTRANSACTIONID uniqueidentifier,
                    LINEITEMID 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, 
                    LINEITEMID,
                    ACCOUNT, 
                    AMOUNT, 
                    PROJECT, 
                    REFERENCE, 
                    TRANSACTIONTYPECODE, 
                    POSTDATE, 
                    GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    ACCOUNTID, 
                    TRANSACTIONAMOUNT, 
                    ORGANIZATIONAMOUNT, 
                    TRANSACTIONCURRENCYID, 
                    BASECURRENCYID, 
                    BASEEXCHANGERATEID, 
                    ORGANIZATIONEXCHANGERATEID,
                    ERRORMESSAGE,
                    MAPPEDVALUES
                )
                select
                    NEWID(),
                    WRITEOFFDISTRIBUTIONS.WRITEOFFID,
                    WRITEOFFDISTRIBUTIONS.WRITEOFFLINEITEMID,
                    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_GENERATEPLEDGEWRITEOFFGLDISTRIBUTION(@REVENUEID, @WRITEOFFIDTABLE) as WRITEOFFDISTRIBUTIONS

                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
                        ,@TRANSACTIONTYPECODE
                        ,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, LINEITEMID, 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
        end