USP_SALESORDER_ONLINE_CLEANUP

            CREATE procedure dbo.USP_SALESORDER_ONLINE_CLEANUP (
                @CHANGEAGENTID uniqueidentifier = null,
                @NUMBERCLEANED integer = 0 output,
                @NUMBERLOGGED integer = 0 output
            )
            as
            begin
                set @NUMBERCLEANED = 0
                set @NUMBERLOGGED = 0
                declare @CURRENTDATE datetime = getdate()

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

                --Get the number of days back we should remove

                --This is determined by the largest expiration date for a payment part 2 part

                declare @DAYSFOREXPIRATION int = null
                select @DAYSFOREXPIRATION = max(DAYSUNTILANONYMOUSUSERCOOKIEEXPIRES)
                from dbo.[SHOPPINGCARTSETTINGS]

                --If there is no payment part 2 setting (or if it's 0), use 30 days

                if @DAYSFOREXPIRATION is null or @DAYSFOREXPIRATION = 0
                    set @DAYSFOREXPIRATION = 30

                declare @ORDERSEXPIREDBEFOREDATE datetime = dateadd(day, -1 * @DAYSFOREXPIRATION, getdate())

                --Orders we're going to delete or log as cannot be deleted for exceptional reasons

                declare 
                    @SALESORDERID uniqueidentifier = null,
                    @ORDERHASPAYMENTS bit = null
                declare EXPIREDORDER_CURSOR cursor local fast_forward for
                select 
                    [ID],
                    case when exists ( --A pending order should not have a payment.  We need to log this special case

                        select 1 from dbo.[SALESORDERPAYMENT] where [SALESORDERID] = [SALESORDER].[ID]
            union
            select 1 from dbo.[SALESORDERBBPAYTRANSACTION] WHERE [SALESORDERBBPAYTRANSACTION].[SALESORDERID] = [SALESORDER].[ID] and [SALESORDERBBPAYTRANSACTION].[CARDCHARGED] = 1
                    ) then 1 else 0 end as [HASPAYMENTS]
                from dbo.[SALESORDER]
                where 
                    [SALESORDER].[SALESMETHODTYPECODE] = 2 and --Online

                    [SALESORDER].[STATUSCODE] = 0 and --Pending

                    [SALESORDER].[DATECHANGED] < @ORDERSEXPIREDBEFOREDATE and --Hasn't changed since the expiration date

                    not exists ( --Is anonymous

                        select 1 from dbo.[CMSUSERSALESORDER] where [SALESORDERID] = [SALESORDER].[ID]
                    ) and
                    not exists ( --No items on order have changed on the order since the expiration date

                        select 1
                        from dbo.[SALESORDERITEM]
                        where 
                            [SALESORDERITEM].[ID] = [SALESORDER].[ID] and
                            [SALESORDERITEM].[DATECHANGED] < @ORDERSEXPIREDBEFOREDATE
                    ) and
                    not exists ( --Not an order that perviously had an issue

                        select 1 
                        from dbo.[SALESORDERCLEANUPERRORLOG] 
                        where [SALESORDERCLEANUPERRORLOG].[ID] = [SALESORDER].[ID]
                    )

                --Starting removal/logging work

                open EXPIREDORDER_CURSOR
                fetch next from EXPIREDORDER_CURSOR into 
                    @SALESORDERID,
                    @ORDERHASPAYMENTS

                while @@FETCH_STATUS = 0
                begin 
                    declare 
                        @ERROR bit = 0,
                        @ERRORCODE tinyint = 0,
                        @ERRORMESSAGE nvarchar(2048) = ''

                    --If the order has a payment, we need to log it instead of deleting

                    if @ORDERHASPAYMENTS = 1
                    begin
                        set @ERROR = 1
                        set @ERRORCODE = 1
                    end
                    else
                    begin
                        begin try
                            --Clear and delete

                            exec dbo.USP_SALESORDER_CLEAR @SALESORDERID, @CHANGEAGENTID, @CURRENTDATE
                            exec dbo.USP_SALESORDER_DELETEBYID_WITHCHANGEAGENTID @SALESORDERID, @CHANGEAGENTID
                        end try
                        begin catch
                            set @ERROR = 1
                            set @ERRORMESSAGE = error_message()
                            --We don't this process to throw errors.

                        end catch
                    end

                    --Insert cleanup error into log

                    if @ERROR = 1
                    begin
                        begin try
                            merge dbo.[SALESORDERCLEANUPERRORLOG] as [TARGET]
                            using (select @SALESORDERID as ID) as [SOURCE]
                            on (TARGET.ID = SOURCE.ID)
                            when matched then
                                update --This update shouldn't happen since orders with errors previously should have been excluded.  Just for sanity.

                                set 
                                    [ERRORCODE] = @ERRORCODE,
                                    [ERRORMESSAGE] = @ERRORMESSAGE,
                                    [CHANGEDBYID] = @CHANGEAGENTID,
                                    [DATECHANGED] = @CURRENTDATE
                            when not matched then
                                insert (
                                    ID,
                                    ERRORCODE,
                                    ERRORMESSAGE,
                                    DATEADDED,
                                    DATECHANGED,
                                    ADDEDBYID,
                                    CHANGEDBYID
                                )
                                values (
                                    @SALESORDERID,
                                    @ERRORCODE,
                                    @ERRORMESSAGE,
                                    @CURRENTDATE,
                                    @CURRENTDATE,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID
                                );

                            set @NUMBERLOGGED += 1
                        end try
                        begin catch
                            --We don't this process to raise errors.  Especially not errors when logging an error.

                        end catch
                    end
                    else 
                        set @NUMBERCLEANED += 1

                    fetch next from EXPIREDORDER_CURSOR into 
                        @SALESORDERID,
                        @ORDERHASPAYMENTS
                end
                close EXPIREDORDER_CURSOR
                deallocate EXPIREDORDER_CURSOR
            end