USP_DATAFORMTEMPLATE_EDIT_COMPLETEDORDER_PATRON

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_COMPLETEDORDER_PATRON (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @CONSTITUENTID uniqueidentifier,
                    @SAMEASPATRON bit,
                    @CREATEADJUSTMENT bit,
                    @ADJUSTMENTREASONCODEID uniqueidentifier,
                    @PREVIOUSCONSTITUENTID uniqueidentifier
                )
                as

                    set nocount on;

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

                    declare @CURRENTDATE datetime
                    set @CURRENTDATE = getdate()

                    declare @PDACCOUNTSYSTEMID uniqueidentifier;
                    declare @ALLOWGLDISTRIBUTIONS bit;

                    select
                        @PDACCOUNTSYSTEMID = ID,
                        @ALLOWGLDISTRIBUTIONS = ALLOWGLDISTRIBUTIONS
                    from
                        dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM();

                    begin try

                        if @SAMEASPATRON = 1
                        begin
                            update dbo.SALESORDER with (rowlock) set
                                CONSTITUENTID = @CONSTITUENTID,
                                RECIPIENTID = @CONSTITUENTID,
                                ADDRESSID = (select top(1) ID from dbo.ADDRESS where CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1 and DONOTMAIL = 0),
                                PHONEID = (select top(1) ID from dbo.PHONE where PHONE.CONSTITUENTID = @CONSTITUENTID and PHONE.ISPRIMARY = 1 and DONOTCALL = 0),
                                EMAILADDRESSID = (select top(1) ID from dbo.EMAILADDRESS where CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1 and DONOTEMAIL = 0),
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where ID = @ID
                        end

                        else
                        begin
                            update dbo.SALESORDER with (rowlock) set
                                CONSTITUENTID = @CONSTITUENTID,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where ID = @ID
                        end

                        -- Update the constituent on 'Order' type revenue transactions for this Sales Order

                        -- Stash the revenue id's in a table so we only hit the sales order table once

                        declare @ORDERREVENUE table(ID uniqueidentifier)

                        insert into @ORDERREVENUE
                        select
                            REVENUE.ID
                        from dbo.REVENUE with (nolock)
                        inner join dbo.SALESORDER
                            on REVENUE.ID = SALESORDER.REVENUEID
                        where SALESORDER.ID = @ID

                        update R with (rowlock) set
                            CONSTITUENTID = @CONSTITUENTID
                        from dbo.REVENUE R
                        where ID in (select ID from @ORDERREVENUE)

                        -- Update recognition credits if necessary

                        update RR with (rowlock) set
                            CONSTITUENTID = @CONSTITUENTID
                        from dbo.REVENUERECOGNITION RR
                        inner join dbo.REVENUESPLIT
                            on RR.REVENUESPLITID = REVENUESPLIT.ID
                        where REVENUESPLIT.REVENUEID in (select ID from @ORDERREVENUE)
                        and RR.CONSTITUENTID = @PREVIOUSCONSTITUENTID

                        -- Update the constituent on all refunds and refund payment revenues

                        -- Stash the credit id's in a table so we only have to mine that table once for multiple updates

                        declare @ORDERREFUNDS table(ID uniqueidentifier)

                        insert into @ORDERREFUNDS
                        select FT.ID
                        from dbo.FINANCIALTRANSACTION FT
                        inner join dbo.CREDIT_EXT EXT on FT.ID = EXT.ID
                        left join dbo.SALESORDER SO on SO.REVENUEID = FT.PARENTID
                        where isnull(SO.ID, EXT.SALESORDERID) = @ID

                        update dbo.FINANCIALTRANSACTION set
                            CONSTITUENTID = @CONSTITUENTID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID in (
                            select ID from @ORDERREFUNDS

                            union all

                            select CREDITPAYMENT.REVENUEID as ID
                            from dbo.CREDITPAYMENT
                            inner join @ORDERREFUNDS as REFUNDS on REFUNDS.ID = CREDITPAYMENT.CREDITID
                        );

                        -- update all sales order payment revenues

                        exec dbo.USP_SALESORDER_UPDATEPAYMENTCONSTITUENT @ID, @CHANGEAGENTID;

                        if @CREATEADJUSTMENT = 1
                        begin

                            declare @ADJUSTMENTREASON nvarchar(300),
                                    @REVENUEID uniqueidentifier,
                                    @REVENUEDATE datetime,
                                    @REVENUEPOSTDATE datetime;

                            set @ADJUSTMENTREASON = (select DESCRIPTION from dbo.ADJUSTMENTREASONCODE where ID = @ADJUSTMENTREASONCODEID)

                            declare POSTED_PAYMENTS cursor local fast_forward for
                            select
                                REVENUE.ID,
                                REVENUE.DATE,
                                REVENUE.POSTDATE
                            from dbo.REVENUE with (nolock)
                            inner join dbo.SALESORDERPAYMENT
                                on REVENUE.ID = SALESORDERPAYMENT.PAYMENTID
                            inner join dbo.REVENUEPOSTED
                                on REVENUEPOSTED.ID = REVENUE.ID
                            where SALESORDERPAYMENT.SALESORDERID = @ID

                            open POSTED_PAYMENTS

                            fetch next from POSTED_PAYMENTS into @REVENUEID, @REVENUEDATE, @REVENUEPOSTDATE

                            while @@fetch_status = 0
                            begin

                                exec dbo.USP_SAVE_ADJUSTMENT 
                                    @REVENUEID, 
                                    null, 
                                    @CHANGEAGENTID, 
                                    @CURRENTDATE,
                                    @REVENUEDATE,
                                    @REVENUEPOSTDATE,
                                    @ADJUSTMENTREASON,
                                    0,
                                    @ADJUSTMENTREASONCODEID

                                if @ALLOWGLDISTRIBUTIONS = 1            
                                    exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION    @REVENUEID, @CHANGEAGENTID, @CURRENTDATE

                                fetch next from POSTED_PAYMENTS into @REVENUEID, @REVENUEDATE, @REVENUEPOSTDATE
                            end
                            --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                            close POSTED_PAYMENTS;
                            deallocate POSTED_PAYMENTS;

                        end


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

                return 0;