USP_SAVE_PLANNEDGIFTPAYOUTGLDISTRIBUTION

CREATE procedure dbo.USP_SAVE_PLANNEDGIFTPAYOUTGLDISTRIBUTION
            (
                @REVENUEID uniqueidentifier,
                @PLANNEDGIFTPAYOUTID 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 @JOURNAL nvarchar(50);
                set @JOURNAL = 'Blackbaud Enterprise';

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

                -- Generate GL distributions for the revenue record.

                insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, POSTDATE, 
                            GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNTID,ERRORMESSAGE,REVENUESPLITID,REVENUEGLDISTRIBUTIONID,
                            BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT,
                            ORGANIZATIONEXCHANGERATEID, MAPPEDVALUES, FINANCIALTRANSACTIONLINEITEMID)
                select 
                    newid(),
                    REVDISTRIBUTIONS.ACCOUNTSTRING,
                    REVDISTRIBUTIONS.AMOUNT,
                    REVDISTRIBUTIONS.PROJECT,
                    REVDISTRIBUTIONS.REFERENCE, 
                    REVDISTRIBUTIONS.TRANSACTIONTYPECODE,
                    REVDISTRIBUTIONS.POSTDATE,
                    REVDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    REVDISTRIBUTIONS.ACCOUNTID,
                    REVDISTRIBUTIONS.ERRORMESSAGE,
                    REVDISTRIBUTIONS.REVENUESPLITID,
                    newid(),
                    REVDISTRIBUTIONS.BASECURRENCYID,
                    REVDISTRIBUTIONS.TRANSACTIONAMOUNT,
                    REVDISTRIBUTIONS.TRANSACTIONCURRENCYID,
                    REVDISTRIBUTIONS.BASEEXCHANGERATEID,
                    REVDISTRIBUTIONS.ORGANIZATIONAMOUNT,
                    REVDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                    REVDISTRIBUTIONS.MAPPEDVALUES,
                    REVDISTRIBUTIONS.FINANCIALTRANSACTIONLINEITEMID
                from 
                (
                select 
                    REVENUE.ID as REVENUEID, 
                    dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, REVENUE.TRANSACTIONTYPECODE, REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    tf.TRANSACTIONTYPECODE, 
                    case when PLANNEDGIFTPAYOUTADJUSTMENT.ID is null then REVENUE.POSTDATE        
                         else PLANNEDGIFTPAYOUTADJUSTMENT.POSTDATE
                    end as POSTDATE,
                    case when PLANNEDGIFTPAYOUTADJUSTMENT.ID is null then (case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end)        -- same reason as above. 

                         else PLANNEDGIFTPAYOUTADJUSTMENT.POSTSTATUSCODE
                    end as POSTSTATUSCODE,
                    tf.ACCOUNTSTRING, 
                    tf.PROJECTCODE as PROJECT, 
                    PLANNEDGIFTPAYOUTSPLIT.AMOUNT, 
                    dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, REVENUEPAYMENTMETHOD.PAYMENTMETHOD, REVENUESPLIT.APPLICATION) + ' Payout' as REFERENCE,
                    tf.ERRORMESSAGE,
                    REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                    REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                    tf.ACCOUNTID as ACCOUNTID,
                    REVENUESPLIT.ID as REVENUESPLITID,
                    PLANNEDGIFTPAYOUTSPLIT.BASECURRENCYID, 
                    PLANNEDGIFTPAYOUTSPLIT.TRANSACTIONAMOUNT, 
                    PLANNEDGIFTPAYOUTSPLIT.TRANSACTIONCURRENCYID, 
                    PLANNEDGIFTPAYOUTSPLIT.BASEEXCHANGERATEID, 
                    PLANNEDGIFTPAYOUTSPLIT.ORGANIZATIONAMOUNT, 
                    PLANNEDGIFTPAYOUTSPLIT.ORGANIZATIONEXCHANGERATEID
                    ,tf.MAPPEDVALUES,
                    PLANNEDGIFTPAYOUTSPLIT.ID as FINANCIALTRANSACTIONLINEITEMID
                from 
                    dbo.REVENUE 
                    left join dbo.REVENUEPOSTED  on REVENUEPOSTED.ID = REVENUE.ID
                    inner join dbo.PLANNEDGIFTPAYOUT on REVENUE.ID = PLANNEDGIFTPAYOUT.REVENUEID 
                    inner join dbo.PLANNEDGIFT  on PLANNEDGIFT.ID = PLANNEDGIFTPAYOUT.ID    
                    inner join dbo.PLANNEDGIFTPAYOUTSPLIT  on REVENUE.ID = PLANNEDGIFTPAYOUTSPLIT.REVENUEID     
                    inner join dbo.REVENUESPLIT  on REVENUE.ID = REVENUESPLIT.REVENUEID and REVENUESPLIT.DESIGNATIONID = PLANNEDGIFTPAYOUTSPLIT.DESIGNATIONID
                    inner join dbo.REVENUEPAYMENTMETHOD  on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID 
                    left join dbo.PLANNEDGIFTPAYOUTADJUSTMENT  on REVENUE.ID = PLANNEDGIFTPAYOUTADJUSTMENT.REVENUEID and PLANNEDGIFTPAYOUTADJUSTMENT.POSTSTATUSCODE <> 0 
                    cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TRANSACTIONTYPECODE,REVENUESPLIT.TYPECODE, 21, PLANNEDGIFT.VEHICLECODE, REVENUESPLIT.DESIGNATIONID) as tf
                where    
                    (REVENUE.ID = @REVENUEID)
                    and (not REVENUESPLIT.APPLICATIONCODE in (1,2,5))                
                ) as REVDISTRIBUTIONS

                declare @ERRORMESSAGE nvarchar(max);
                select top 1 @ERRORMESSAGE = ERRORMESSAGE from @DISTRIBUTIONS where nullif(ERRORMESSAGE,'') is not null and ACCOUNTID is 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
                        @REVENUEID
                        ,(select TRANSACTIONTYPECODE from dbo.REVENUE where ID = @REVENUEID)
                        ,D.ERRORMESSAGE
                        ,convert(nvarchar(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 = 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)

        --Check to see if there is an adjustment, if so, use adjustment poststatuscode

        DECLARE @ADJUSTMENTPOSTSTATUSCODE tinyint
        SET @ADJUSTMENTPOSTSTATUSCODE = 1
        if EXISTS (SELECT ID FROM ADJUSTMENT WHERE REVENUEID = @REVENUEID)
          BEGIN
             SELECT @ADJUSTMENTPOSTSTATUSCODE = POSTSTATUSCODE FROM ADJUSTMENT WHERE REVENUEID = @REVENUEID
          END

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

        insert into dbo.JOURNALENTRY_EXT
            (ID, JOURNAL, TABLENAMECODE, GLPAYMENTMETHODREVENUETYPEMAPPINGID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, LOGICALREVENUEID, ACCOUNT,
            PRECALCPOSTSTATUSCODE, PLANNEDGIFTPAYOUTID, BENEFITTYPECODE, PRECALCBASEEXCHANGERATEID, PRECALCORGANIZATIONEXCHANGERATEID)
        select
            D.GLTRANSACTIONID, @JOURNAL, 9, D.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, @REVENUEID, D.ACCOUNT,
            1, @PLANNEDGIFTPAYOUTID, 4, D.BASEEXCHANGERATEID, D.ORGANIZATIONEXCHANGERATEID
        from
            @DISTRIBUTIONS D