USP_SAVE_UNREALIZEDGAINLOSSGLDISTRIBUTION

    CREATE procedure dbo.USP_SAVE_UNREALIZEDGAINLOSSGLDISTRIBUTION
    (
        @REVENUEID uniqueidentifier,
        @CHANGEAGENTID uniqueidentifier = null,
        @CHANGEDATE datetime = null,
        @POSTDATE datetime = null
    )
    as
        set nocount on;

        if exists(select 1 from dbo.REVENUECOMMITMENTREVALUATION where REVENUEID = @REVENUEID)
        begin

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

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

            if @POSTDATE is null
                set @POSTDATE = @CHANGEDATE;

            declare @CUSTOMIZED integer = 0;
            exec  @CUSTOMIZED = dbo.USP_SAVE_UNREALIZEDGAINLOSSGLDISTRIBUTION_CUSTOMIZE @REVENUEID,@CHANGEAGENTID,@CHANGEDATE,@POSTDATE

            if @CUSTOMIZED = 0
            begin

                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,
                    ACCOUNTID uniqueidentifier,
                    ERRORMESSAGE nvarchar(max),
                    REVENUESPLITID uniqueidentifier,
                    REVENUEGLDISTRIBUTIONID uniqueidentifier,
                    BASECURRENCYID uniqueidentifier,
                    ORGANIZATIONAMOUNT money,
                    SYSTEMDISTRIBUTION bit default 0,
                    MAPPEDVALUES xml            
                );

                insert into @DISTRIBUTIONS
                (
                    GLTRANSACTIONID, 
                    ACCOUNT, 
                    AMOUNT, 
                    PROJECT, 
                    REFERENCE, 
                    TRANSACTIONTYPECODE, 
                    POSTDATE, 
                    ACCOUNTID,
                    ERRORMESSAGE,
                    REVENUESPLITID,
                    REVENUEGLDISTRIBUTIONID,
                    BASECURRENCYID, 
                    ORGANIZATIONAMOUNT, 
                    SYSTEMDISTRIBUTION,
                    MAPPEDVALUES
                )
                select 
                    newid(),
                    GAINLOSSDISTRIBUTIONS.ACCOUNTSTRING,
                    GAINLOSSDISTRIBUTIONS.AMOUNT,
                    GAINLOSSDISTRIBUTIONS.PROJECT,
                    GAINLOSSDISTRIBUTIONS.REFERENCE, 
                    GAINLOSSDISTRIBUTIONS.TRANSACTIONTYPECODE,
                    GAINLOSSDISTRIBUTIONS.POSTDATE,
                    GAINLOSSDISTRIBUTIONS.ACCOUNTID,
                    GAINLOSSDISTRIBUTIONS.ERRORMESSAGE,
                    GAINLOSSDISTRIBUTIONS.REVENUESPLITID,
                    newid(),
                    GAINLOSSDISTRIBUTIONS.BASECURRENCYID,
                    GAINLOSSDISTRIBUTIONS.ORGANIZATIONAMOUNT,
                    1,
                    GAINLOSSDISTRIBUTIONS.MAPPEDVALUES
                from 
                    dbo.UFN_REVENUE_GENERATEUNREALIZEDGAINLOSSGLDISTRIBUTION(@REVENUEID,@POSTDATE) as GAINLOSSDISTRIBUTIONS 
                where 
                    not exists(
                        select ID 
                        from dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION with (nolock) 
                        where UNREALIZEDGAINLOSSGLDISTRIBUTION.REVENUEID = GAINLOSSDISTRIBUTIONS.REVENUEID 
                            and UNREALIZEDGAINLOSSGLDISTRIBUTION.OUTDATED = 0
                    )
                    and GAINLOSSDISTRIBUTIONS.POSTSTATUSCODE > 0

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

                if @ERRORMESSAGE <> ''
                begin
                    raiserror('%s', 13, 1, @ERRORMESSAGE);
                    return 1;
                end
                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)

                --Bug 70136 - Need adjustment post status code to determine if date should be checked. Will be null if DNP.

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

                declare @ADJUSTMENTPOSTSTATUSCODE tinyint
                -- Bug 82359 ~ No need to check post date if Revenue record is Do Not Post unless Adjustment needs posting.

                declare @DONOTPOST tinyint
                select @DONOTPOST = DONOTPOST from REVENUE with (nolock) where ID = @REVENUEID
                if @DONOTPOST = 1
                    set @ADJUSTMENTPOSTSTATUSCODE = 2
                else
                    set @ADJUSTMENTPOSTSTATUSCODE = 1        

                if exists (select ID from dbo.UNREALIZEDGAINLOSSADJUSTMENT with (nolock) where REVENUEID = @REVENUEID)
                begin
                    select @ADJUSTMENTPOSTSTATUSCODE = POSTSTATUSCODE from dbo.UNREALIZEDGAINLOSSADJUSTMENT with (nolock) where REVENUEID = @REVENUEID
                end

                if exists(select * from @DISTRIBUTIONS) and @ADJUSTMENTPOSTSTATUSCODE <> 2
                    set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE((select top 1 POSTDATE from @DISTRIBUTIONS));

                if @ERRORMESSAGE <> ''
                begin
                    raiserror(@ERRORMESSAGE, 13, 1);          
                    return 1;
                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)

                -- Insert rows in GLTRANSACTION table

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

                -- Insert rows in UNREALIZEDGAINLOSSGLDISTRIBUTION table

                insert into dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION
                    (ID, REVENUEID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, 
                        GLTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID, 
                        ORGANIZATIONAMOUNT)
                select
                    REVENUEGLDISTRIBUTIONID,
                    @REVENUEID,
                    PROJECT,
                    REFERENCE, 
                    AMOUNT,
                    ACCOUNT,
                    TRANSACTIONTYPECODE,
                    GLTRANSACTIONID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CHANGEDATE,
                    @CHANGEDATE,
                    BASECURRENCYID,
                    ORGANIZATIONAMOUNT
                from 
                    @DISTRIBUTIONS;    
            end
        end