USP_GLOBALCHANGE_SPONSORSHIPSALESORDERUNLOCK

    CREATE procedure dbo.USP_GLOBALCHANGE_SPONSORSHIPSALESORDERUNLOCK
    (
        @CHANGEAGENTID uniqueidentifier = null,
        @ASOF as datetime = null,
        @NUMBERADDED int = 0 output,
        @NUMBEREDITED int = 0 output,
        @NUMBERDELETED int = 0 output
    )
    as
        set nocount off;

        declare @CURRENTDATE datetime
        declare @EXPIREDITEMCOUNT int
        declare @SO_ITEMID uniqueidentifier
        set @CURRENTDATE = getdate();
        set @NUMBERADDED = 0;
        set @NUMBEREDITED = 0;
        set @NUMBERDELETED = 0;

        begin try

            -- We want to delete pending sponsorship salesorders that have expired.

            -- leaving these salesorder records makes the linked sponsorship opportunities uneditable.

            declare SALESORDERITEM_CURSOR cursor local fast_forward for
            select sori.ID
            from dbo.SALESORDERRESERVEDITEM sori 
            inner join dbo.SALESORDERITEM soi on sori.ID = soi.ID
            inner join dbo.SALESORDER so on so.ID = soi.SALESORDERID
            where soi.TYPECODE = 12  -- sponsorship

              and DATEDIFF(ss, sori.EXPIRATIONDATE, @CURRENTDATE) > 0 
              and SO.STATUSCODE = 0 -- pending


            open SALESORDERITEM_CURSOR
            fetch next from SALESORDERITEM_CURSOR into @SO_ITEMID

            while (@@FETCH_STATUS = 0)
            begin

                exec dbo.USP_SALESORDERITEM_DELETE @SO_ITEMID, null 

                set @NUMBEREDITED = @NUMBEREDITED + 1        

                fetch next from SALESORDERITEM_CURSOR into @SO_ITEMID
            end

            close SALESORDERITEM_CURSOR
            deallocate SALESORDERITEM_CURSOR

        end try

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