USP_DATAFORMTEMPLATE_ADJUST_STOCKSALEGLDISTRIBUTION_2

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADJUST_STOCKSALEGLDISTRIBUTION_2
                    (
                        @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 @STOCKSALEADJUSTMENTID 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)

                        --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 = b.REVENUEID from STOCKSALE as a join REVENUEPAYMENTMETHOD as b on a.STOCKDETAILID = b.ID where a.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.STOCKSALEADJUSTMENT as ADJUSTMENT
                                    where STOCKSALEID = @ID and POSTSTATUSCODE = 1)
                        begin
                            set @ADJUST = 1;
                            set @ADJUSTMENTEXISTS = 1;
                        end

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

                            declare @STOCKDETAILID uniqueidentifier;
                            select @STOCKDETAILID = STOCKDETAILID
                            from dbo.STOCKSALE
                            where ID = @ID

                            set @ADJUST = dbo.UFN_STOCKDETAIL_DISTRIBUTIONCHANGED(@STOCKDETAILID, @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 @STOCKSALEADJUSTMENTID = null;
                            exec dbo.USP_SAVE_STOCKSALEADJUSTMENT @ID, @STOCKSALEADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, 
                                @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID;

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

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

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

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

                            -- If an unposted adjustment already exists, update the stock sale GL distribution rows.

                            -- Otherwise, insert new stock sale gl distribution rows using @GLDISTRIBUTION.

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

                                declare @REVENUEID uniqueidentifier;
                                select @REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
                                from dbo.STOCKSALE
                                inner join REVENUEPAYMENTMETHOD on STOCKSALE.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
                                where STOCKSALE.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 stock detail record.

                                insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID,OLDGLTRANSACTIONID,OLDFTLID)
                                select 
                                    newid(),
                                    STOCKDISTRIBUTIONS.ACCOUNT,
                                    STOCKDISTRIBUTIONS.AMOUNT,
                                    STOCKDISTRIBUTIONS.PROJECT,
                                    STOCKDISTRIBUTIONS.REFERENCE, 
                                    STOCKDISTRIBUTIONS.TRANSACTIONTYPECODE,
                                    STOCKDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                                    STOCKDISTRIBUTIONS.ORGANIZATIONAMOUNT,
                                    STOCKDISTRIBUTIONS.BASECURRENCYID,
                                    STOCKDISTRIBUTIONS.TRANSACTIONAMOUNT,
                                    STOCKDISTRIBUTIONS.TRANSACTIONCURRENCYID,
                                    STOCKDISTRIBUTIONS.BASEEXCHANGERATEID,
                                    STOCKDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                  STOCKDISTRIBUTIONS.ID,
                  JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
                                from 
                                    dbo.UFN_REVENUE_GETSTOCKSALEGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION) STOCKDISTRIBUTIONS
                  join dbo.JOURNALENTRY on STOCKDISTRIBUTIONS.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, STOCKSALEID,PAYMENTMETHODCODE)
                      select 
                           T1.GLTRANSACTIONID
                          ,T1.GLTRANSACTIONID
                          ,11
                          ,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 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_STOCKSALE_SAVEHISTORY @ID, @CHANGEAGENTID, @CHANGEDATE, @STOCKSALEADJUSTMENTID;
                        end
                    end try

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

                    return 0;