USP_DATAFORMTEMPLATE_ADJUST_GIFTINKINDSALEGLDISTRIBUTION

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

                    declare @ADJUST bit;
                    declare @ADJUSTMENTEXISTS bit;
                    declare @GIFTINKINDSALEADJUSTMENTID uniqueidentifier;

                    set @ADJUST = 0;
                    set @ADJUSTMENTEXISTS = 0;

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

                    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);

                        declare @REVID uniqueidentifier;
                        select @REVID = REVENUEPAYMENTMETHOD.REVENUEID from dbo.GIFTINKINDSALE join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID where GIFTINKINDSALE.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 exists (    select 1
                                    from dbo.GIFTINKINDSALEADJUSTMENT as ADJUSTMENT
                                    where GIFTINKINDSALEID = @ID and POSTSTATUSCODE = 1)
                        begin
                            set @ADJUST = 1;
                            set @ADJUSTMENTEXISTS = 1;
                        end

                        /* Distributions Changed */
                        if @ADJUST = 0
                        begin

                            declare @GIFTINKINDPAYMENTMETHODDETAILID uniqueidentifier;
                            select @GIFTINKINDPAYMENTMETHODDETAILID = GIFTINKINDPAYMENTMETHODDETAILID
                            from dbo.GIFTINKINDSALE
                            where ID = @ID;

                            set @ADJUST = dbo.UFN_GIFTINKINDPAYMENTMETHODDETAIL_DISTRIBUTIONCHANGED(@GIFTINKINDPAYMENTMETHODDETAILID, @GLDISTRIBUTION);
                        end

                        /* 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 @GIFTINKINDSALEADJUSTMENTID = null;
                            exec dbo.USP_SAVE_GIFTINKINDSALEADJUSTMENT @ID, @GIFTINKINDSALEADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, 
                                @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID;

                            declare @DEFAULTTRANSACTIONCURRENCYID uniqueidentifier;
                            declare @BASECURRENCYID uniqueidentifier;
                            declare @BASEEXCHANGERATEID uniqueidentifier;
                            declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

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

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

                            declare @POSTDATE datetime;
                            select @POSTDATE = SALEPOSTDATE from dbo.GIFTINKINDSALE where ID = @ID;

                            -- If an unposted adjustment already exists, update the gift-in-kind sale GL distribution rows.

                            -- Otherwise, insert new gift-in-kind sale gl distribution rows using @GLDISTRIBUTION.

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

                                declare @REVENUEID uniqueidentifier;
                                select @REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
                                from dbo.GIFTINKINDSALE
                                inner join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID
                                where GIFTINKINDSALE.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 gift-in-kind detail record.

                                insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID,OLDGLTRANSACTIONID,OLDFTLID)
                                select 
                                    newid(),
                                    GIFTINKINDDISTRIBUTIONS.ACCOUNT,
                                    GIFTINKINDDISTRIBUTIONS.AMOUNT,
                                    GIFTINKINDDISTRIBUTIONS.PROJECT,
                                    GIFTINKINDDISTRIBUTIONS.REFERENCE, 
                                    GIFTINKINDDISTRIBUTIONS.TRANSACTIONTYPECODE,
                                    GIFTINKINDDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                                    GIFTINKINDDISTRIBUTIONS.ORGANIZATIONAMOUNT,
                                    GIFTINKINDDISTRIBUTIONS.BASECURRENCYID,
                                    GIFTINKINDDISTRIBUTIONS.TRANSACTIONAMOUNT,
                                    GIFTINKINDDISTRIBUTIONS.TRANSACTIONCURRENCYID,
                                    GIFTINKINDDISTRIBUTIONS.BASEEXCHANGERATEID,
                                    GIFTINKINDDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                  GIFTINKINDDISTRIBUTIONS.ID,
                  JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID                  

                                from 
                                    dbo.UFN_REVENUE_GETGIFTINKINDSALEGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION) GIFTINKINDDISTRIBUTIONS
                  join dbo.JOURNALENTRY on GIFTINKINDDISTRIBUTIONS.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, GIFTINKINDSALEID,PAYMENTMETHODCODE)
                      select 
                           T1.GLTRANSACTIONID
                          ,T1.GLTRANSACTIONID
                          ,13
                          ,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_GIFTINKINDSALE_SAVEHISTORY @ID, @CHANGEAGENTID, @CHANGEDATE, @GIFTINKINDSALEADJUSTMENTID;
                        end
                    end try

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

                    return 0;