USP_SAVE_PROPERTYDETAILGLDISTRIBUTION

            CREATE procedure [dbo].[USP_SAVE_PROPERTYDETAILGLDISTRIBUTION]
            (
                @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_PROPERTYDETAILGLDISTRIBUTION_CUSTOMIZE @REVENUEID,@CHANGEAGENTID,@CHANGEDATE

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

                    declare @DISTRIBUTIONS table(
                        PROPERTYDETAILID 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,
                        POSTSTATUSCODE tinyint,
                        ERRORMESSAGE nvarchar(max),
                        MAPPEDVALUES xml,
                        SOURCELINEITEMID uniqueidentifier,
                        REVENUESPLITID uniqueidentifier                
                    );

                    -- Generate GL distributions for the property detail record.

                    insert into @DISTRIBUTIONS(GLTRANSACTIONID, PROPERTYDETAILID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, POSTDATE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, ACCOUNTID, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, TRANSACTIONCURRENCYID, BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, POSTSTATUSCODE, ERRORMESSAGE, MAPPEDVALUES, SOURCELINEITEMID)
                    select 
                        newid(),
                        PROPERTYDISTRIBUTIONS.REVENUEPAYMENTMETHODID,
                        PROPERTYDISTRIBUTIONS.ACCOUNTSTRING,
                        PROPERTYDISTRIBUTIONS.AMOUNT,
                        PROPERTYDISTRIBUTIONS.PROJECT,
                        PROPERTYDISTRIBUTIONS.REFERENCE, 
                        PROPERTYDISTRIBUTIONS.TRANSACTIONTYPECODE,
                        PROPERTYDISTRIBUTIONS.POSTDATE,
                        PROPERTYDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        PROPERTYDISTRIBUTIONS.ACCOUNTID,
                        PROPERTYDISTRIBUTIONS.TRANSACTIONAMOUNT,
                        PROPERTYDISTRIBUTIONS.ORGANIZATIONAMOUNT,
                        PROPERTYDISTRIBUTIONS.TRANSACTIONCURRENCYID,
                        PROPERTYDISTRIBUTIONS.BASECURRENCYID,
                        PROPERTYDISTRIBUTIONS.BASEEXCHANGERATEID,
                        PROPERTYDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                        PROPERTYDISTRIBUTIONS.POSTSTATUSCODE,
                        PROPERTYDISTRIBUTIONS.ERRORMESSAGE,
                        PROPERTYDISTRIBUTIONS.MAPPEDVALUES,
                        PROPERTYDISTRIBUTIONS.REVENUESPLITID
                    from 
                        dbo.UFN_REVENUE_GENERATEPROPERTYDETAILGLDISTRIBUTION(@REVENUEID) as PROPERTYDISTRIBUTIONS
                    where not exists
                        (select 1 
                            from dbo.JOURNALENTRY JE
                            inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
                            where (JEX.PROPERTYDETAILID = PROPERTYDISTRIBUTIONS.REVENUEPAYMENTMETHODID or LI.FINANCIALTRANSACTIONID = PROPERTYDISTRIBUTIONS.REVENUEPAYMENTMETHODID)
                                and LI.TYPECODE != 1 and LI.DELETEDON is null and JEX.OUTDATED = 0)
                        and PROPERTYDISTRIBUTIONS.POSTSTATUSCODE > 0;

                    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)

                    select top 1 @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(POSTDATE)
                    from @DISTRIBUTIONS
                    where
                        POSTSTATUSCODE <> 2 and
                        dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(POSTDATE) <> ''

                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)

            if exists(select 1 from @DISTRIBUTIONS)
            begin

              declare @Split table(SALEID uniqueidentifier,SOURCEID uniqueidentifier,FTLIID uniqueidentifier,    AMOUNT money,TRANSACTIONAMOUNT money,ORGANIZATIONAMOUNT money, POSTDATE datetime,POSTSTATUSCODE tinyint)

              insert into @Split(SALEID,SOURCEID,FTLIID,POSTDATE,POSTSTATUSCODE,AMOUNT,TRANSACTIONAMOUNT,ORGANIZATIONAMOUNT) 
              select  D.PROPERTYDETAILID,D.SOURCELINEITEMID, isnull(FINANCIALTRANSACTIONLINEITEM.ID, newID()),D.POSTDATE,isnull(FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE,1),
              sum(D.AMOUNT), sum(D.TRANSACTIONAMOUNT), sum(D.ORGANIZATIONAMOUNT)
              from @DISTRIBUTIONS D 
              left outer join dbo.FINANCIALTRANSACTIONLINEITEM  on D.SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID and D.PROPERTYDETAILID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
              where D.TRANSACTIONTYPECODE = 0 and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null 
              group by D.PROPERTYDETAILID,D.SOURCELINEITEMID,D.REVENUESPLITID,D.POSTDATE,FINANCIALTRANSACTIONLINEITEM.ID,FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE

              update D set D.REVENUESPLITID = S.FTLIID from @DISTRIBUTIONS D join @Split S on D.SOURCELINEITEMID = S.SOURCEID 

              merge dbo.FINANCIALTRANSACTIONLINEITEM as target
              using @Split as source 
              left outer join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = source.FTLIID
              on (target.ID = source.FTLIID)
              when matched then update set
              target.TRANSACTIONAMOUNT = source.AMOUNT
              ,target.BASEAMOUNT = source.TRANSACTIONAMOUNT
              ,target.ORGAMOUNT = source.ORGANIZATIONAMOUNT
        ,target.POSTDATE = source.POSTDATE
              when not matched by target then 
              insert 
              (ID,FINANCIALTRANSACTIONID,SOURCELINEITEMID,TRANSACTIONAMOUNT,VISIBLE,DESCRIPTION
              ,SEQUENCE,TYPECODE,POSTDATE,POSTSTATUSCODE,BASEAMOUNT,ORGAMOUNT
              -- Boilerplate

              ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              values  
              (source.FTLIID,source.SALEID,source.SOURCEID,source.AMOUNT,1,'Sold Stock',1,0,source.POSTDATE,1,source.TRANSACTIONAMOUNT,source.ORGANIZATIONAMOUNT
              ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);


              update FINANCIALTRANSACTIONLINEITEM set  FINANCIALTRANSACTIONLINEITEM.DELETEDON = @CHANGEDATE
              ,FINANCIALTRANSACTIONLINEITEM.TYPECODE = 99        --orphaned

              ,FINANCIALTRANSACTIONLINEITEM.CHANGEDBYID = @CHANGEAGENTID, FINANCIALTRANSACTIONLINEITEM.DATECHANGED = @CHANGEDATE
              from dbo.FINANCIALTRANSACTION 
              join  FINANCIALTRANSACTIONLINEITEM  on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
              left outer join dbo.JOURNALENTRY  on FINANCIALTRANSACTIONLINEITEM.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
              left outer join @Split S on FINANCIALTRANSACTIONLINEITEM.ID = S.FTLIID
              where FINANCIALTRANSACTION.PARENTID = @REVENUEID
              and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0 and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1 and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
              and FINANCIALTRANSACTIONLINEITEM.REVERSEDLINEITEMID is null and JOURNALENTRY.ID is null and S.FTLIID is null


                --Change to FTM.  First change is just do a single insert into JournalEntry                 

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

                insert into dbo.JOURNALENTRY_EXT (ID, DISTRIBUTIONTABLEID, TABLENAMECODE, PROJECT, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, PRECALCPOSTSTATUSCODE, PRECALCORGANIZATIONEXCHANGERATEID, PRECALCBASEEXCHANGERATEID,  ACCOUNT, GLPAYMENTMETHODREVENUETYPEMAPPINGID, LOGICALREVENUEID, PROPERTYDETAILID,PAYMENTMETHODCODE)
                select
                     T1.GLTRANSACTIONID
                    ,T1.GLTRANSACTIONID
                    ,10
                    ,T1.PROJECT
                    ,@JOURNAL
                    ,@CHANGEAGENTID
                    ,@CHANGEAGENTID
                    ,@CHANGEDATE
                    ,@CHANGEDATE
                    ,1
                    ,T1.ORGANIZATIONEXCHANGERATEID
                    ,T1.BASEEXCHANGERATEID
                    ,T1.ACCOUNT
                    ,T1.GLPAYMENTMETHODREVENUETYPEMAPPINGID
                    ,@REVENUEID
                    ,T1.PROPERTYDETAILID
                    ,isnull(T2.PAYMENTMETHODCODE,1)
                from @DISTRIBUTIONS T1
                left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING T2 on T1.GLPAYMENTMETHODREVENUETYPEMAPPINGID = T2.ID
            end
        end