USP_DATAFORMTEMPLATE_EDIT_ADJUSTPROPERTYDETAILGLDISTRIBUTION_2

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_ADJUSTPROPERTYDETAILGLDISTRIBUTION_2
                    (
                        @ID uniqueidentifier,
                        @GLDISTRIBUTION xml,
                        @ADJUSTMENTDATE datetime,
                        @ADJUSTMENTPOSTDATE datetime,
                        @ADJUSTMENTREASON nvarchar(300),
                        @CHANGEAGENTID uniqueidentifier = null,
                        @ADJUSTMENTREASONCODEID uniqueidentifier
                    )
                as
                    set nocount on;

                    declare @ADJUST bit;
                    set @ADJUST = 0;

                    declare @ADJUSTMENTEXISTS bit;
                    set @ADJUSTMENTEXISTS = 0;

                    declare @CHANGEDATE datetime;
                    set @CHANGEDATE = getdate();

                    declare @PROPERTYDETAILADJUSTMENTID uniqueidentifier;

                    begin try

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

                        if (dbo.UFN_GLDISTRIBUTION_DEBITSEQUALCREDITS(@GLDISTRIBUTION) = 0)
                            raiserror('The sum of the debit accounts must equal the sum of the credit accounts.', 13, 1)

                        --if (dbo.UFN_GLDISTRIBUTION_ACCOUNTEXISTS(@GLDISTRIBUTION) = 0)

                        --    raiserror('One or more of the edited accounts do not exist.', 13, 1)


                        declare @REVID uniqueidentifier
                        select @REVID = REVENUEID from dbo.REVENUEPAYMENTMETHOD where ID = @ID

                        if (dbo.UFN_GLDISTRIBUTION_ACCOUNTEXISTS_2(@GLDISTRIBUTION,@REVID) = 0)
                            raiserror('One or more of the edited accounts do not exist.', 13, 1)                            


                        /* Already adjusted */
                        if (select COUNT(ADJUSTMENT.ID)
                                from dbo.PROPERTYDETAILADJUSTMENT as ADJUSTMENT
                                where PROPERTYDETAILID = @ID and POSTSTATUSCODE = 1) > 0
                        begin
                            set @ADJUST = 1;
                            set @ADJUSTMENTEXISTS = 1;
                        end

                        /* Distributions Changed */
                        if @ADJUST = 0
                            set @ADJUST = dbo.UFN_PROPERTYDETAIL_DISTRIBUTIONCHANGED(@ID, @GLDISTRIBUTION)

                        /* If there was a change to GL related data log an adjustment */
                        if @ADJUST = 1
                        begin
                            if @ADJUSTMENTREASONCODEID is null
                                raiserror('BBERR_ADJUSTMENTREASONCODEIDREQUIRED', 13, 1)

                            set @PROPERTYDETAILADJUSTMENTID = null;
                            exec dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT @ID, @PROPERTYDETAILADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, 
                                @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID;

                            declare @TRANSACTIONCURRENCYID uniqueidentifier;
                            declare @BASECURRENCYID uniqueidentifier;
                            declare @BASEEXCHANGERATEID uniqueidentifier;
                            declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

                            select 
                                @TRANSACTIONCURRENCYID = PROPERTYDETAIL.TRANSACTIONCURRENCYID,
                                @BASECURRENCYID = PROPERTYDETAIL.BASECURRENCYID,
                                @BASEEXCHANGERATEID = PROPERTYDETAIL.BASEEXCHANGERATEID,
                                @ORGANIZATIONEXCHANGERATEID = PROPERTYDETAIL.ORGANIZATIONEXCHANGERATEID
                            from dbo.PROPERTYDETAIL
                            where PROPERTYDETAIL.ID = @ID;

                            set @GLDISTRIBUTION = dbo.UFN_GLDISTRIBUTION_CONVERTAMOUNTSINXML(@GLDISTRIBUTION, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONEXCHANGERATEID, @BASEEXCHANGERATEID);

                            -- If an unposted adjustment already exists, update the property detail GL distribution rows.

                            -- Otherwise, insert new property detail gl distribution rows using @GLDISTRIBUTION.

                            if @ADJUSTMENTEXISTS = 1 
                            begin
                                exec dbo.USP_REVENUE_GETPROPERTYDETAILGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2 @ID, @GLDISTRIBUTION, @ADJUSTMENTPOSTDATE, @CHANGEAGENTID;
                            end
                            else
                            begin
                                declare @JOURNAL nvarchar(50);
                                set @JOURNAL = 'Blackbaud Enterprise';

                                set @CHANGEDATE = getdate();

                                declare @REVENUEID uniqueidentifier;
                                select @REVENUEID = REVENUEID from dbo.REVENUEPAYMENTMETHOD where ID = @ID;

                                declare @DISTRIBUTIONS table(
                                    GLTRANSACTIONID uniqueidentifier,               
                                    ACCOUNT nvarchar(100),
                                    AMOUNT money,
                                    PROJECT nvarchar(100),
                                    REFERENCE nvarchar(255),
                                    TRANSACTIONTYPECODE tinyint,
                                    GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
                                    ORGANIZATIONAMOUNT money,
                                    BASECURRENCYID uniqueidentifier,
                                    TRANSACTIONAMOUNT money,
                                    TRANSACTIONCURRENCYID uniqueidentifier,
                                    BASEEXCHANGERATEID uniqueidentifier,
                                    ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                  OLDGLTRANSACTIONID uniqueidentifier,
                  OLDFTLID uniqueidentifier,
                  NEWFTLID uniqueidentifier
                                );

                                -- Get the user-defined GL distributions for the property detail record.

                                insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, OLDGLTRANSACTIONID,OLDFTLID)
                                select 
                                    newid(),
                                    PROPERTYDISTRIBUTIONS.ACCOUNT,
                                    PROPERTYDISTRIBUTIONS.AMOUNT,
                                    PROPERTYDISTRIBUTIONS.PROJECT,
                                    PROPERTYDISTRIBUTIONS.REFERENCE, 
                                    PROPERTYDISTRIBUTIONS.TRANSACTIONTYPECODE,
                                    PROPERTYDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                                    PROPERTYDISTRIBUTIONS.ORGANIZATIONAMOUNT,
                                    PROPERTYDISTRIBUTIONS.BASECURRENCYID,
                                    PROPERTYDISTRIBUTIONS.TRANSACTIONAMOUNT,
                                    PROPERTYDISTRIBUTIONS.TRANSACTIONCURRENCYID,
                                    PROPERTYDISTRIBUTIONS.BASEEXCHANGERATEID,
                                    PROPERTYDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                  PROPERTYDISTRIBUTIONS.ID,
                  JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
                                from 
                                    dbo.UFN_REVENUE_GETPROPERTYDETAILGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION) PROPERTYDISTRIBUTIONS
                  join dbo.JOURNALENTRY on PROPERTYDISTRIBUTIONS.ID = JOURNALENTRY.ID

                        declare @Split table(FTLIID uniqueidentifier,    NEWFTLIID uniqueidentifier,    OLDSOURCEID uniqueidentifier)

                        insert into @Split(FTLIID) 
                        select  distinct D.OLDFTLID
                        from @DISTRIBUTIONS D 


                  update @Split set NEWFTLIID = newid();

                  update S set S.OLDSOURCEID = L2.SOURCELINEITEMID
                  from @Split S 
                  join dbo.FINANCIALTRANSACTIONLINEITEM L1 on S.FTLIID = L1.ID
                  join dbo.FINANCIALTRANSACTIONLINEITEM L2 on L2.ID = L1.REVERSEDLINEITEMID                  

                  update D set D.NEWFTLID = S.NEWFTLIID
                  from @DISTRIBUTIONS D join @Split S on S.FTLIID = D.OLDFTLID


                        insert into dbo.FINANCIALTRANSACTIONLINEITEM
                        (ID,FINANCIALTRANSACTIONID,SOURCELINEITEMID,TRANSACTIONAMOUNT,VISIBLE,DESCRIPTION
                        ,SEQUENCE,TYPECODE,POSTDATE,POSTSTATUSCODE,BASEAMOUNT,ORGAMOUNT
                        ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)

                  select S.NEWFTLIID
                  ,FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                  ,S.OLDSOURCEID
                  ,FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
                  ,FINANCIALTRANSACTIONLINEITEM.VISIBLE
                  ,FINANCIALTRANSACTIONLINEITEM.DESCRIPTION
                        ,FINANCIALTRANSACTIONLINEITEM.SEQUENCE
                  ,FINANCIALTRANSACTIONLINEITEM.TYPECODE
                  ,@ADJUSTMENTPOSTDATE
                  ,1
                  ,FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
                  ,FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT
                        ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                  from dbo.FINANCIALTRANSACTIONLINEITEM 
                  join @Split S on S.FTLIID = FINANCIALTRANSACTIONLINEITEM.ID


                        --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
                            ,NEWFTLID
                            ,TRANSACTIONTYPECODE
                            ,AMOUNT
                            ,REFERENCE
                  ,@ADJUSTMENTPOSTDATE
                            --,POSTDATE

                            ,@CHANGEAGENTID
                            ,@CHANGEAGENTID
                            ,@CHANGEDATE
                            ,@CHANGEDATE
                            --,ACCOUNTID

                  ,dbo.UFN_GLACCOUNT_GETIDFROMACCOUNTANDREVENUE(D.ACCOUNT,@REVENUEID)                
                            ,ORGANIZATIONAMOUNT
                            ,TRANSACTIONAMOUNT
                            ,TRANSACTIONCURRENCYID
                            ,TRANSACTIONTYPECODE
                        from @DISTRIBUTIONS D

                        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
                            ,@ID  
                          ,isnull(T2.PAYMENTMETHODCODE,1)
                        from @DISTRIBUTIONS T1
                        left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING T2 on T1.GLPAYMENTMETHODREVENUETYPEMAPPINGID = T2.ID

                      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
                      where FINANCIALTRANSACTION.PARENTID = @REVENUEID
                      and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0 and  JOURNALENTRY.ID is null; 

                            end

                            exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY @ID, @CHANGEAGENTID, null, @PROPERTYDETAILADJUSTMENTID;
                        end
                    end try

                    begin catch
                        exec dbo.USP_RAISE_ERROR;
                        return 1;
                    end catch

                    return 0;