USP_BUSINESSPROCESSSTATUS_BULKDELETE

    create procedure dbo.USP_BUSINESSPROCESSSTATUS_BULKDELETE
    (
     @CHANGEAGENTID uniqueidentifier = null,
     @NUMBERDELETED integer = 0 output,
     @SELECTIONID uniqueidentifier = null,
     @DATEPART tinyint = 3,
     @DATEINTERVAL integer = 1,
     @NUMBERTOKEEP integer = 0 -- number to keep, if set to a non-zero value, trumps date parameters

    )
    as begin
    set nocount on;

    declare @CUTOFF datetime;
    declare @FILTERDATE datetime;
    declare @PREVIOUSFILTERDATE datetime;
    declare @MAXDATE datetime;

    set @NUMBERDELETED = 0;

    begin try

        if isnull(@NUMBERTOKEEP, 0) < 0
            raiserror('BBERR_INVALIDNUMBERTOKEEP', 13, 1);

        if isnull(@DATEINTERVAL, 0) < 0
            raiserror('BBERR_INVALIDDATEINTERVAL', 13, 1);

        if isnull(@NUMBERTOKEEP, 0) = 0 
        begin
            select @CUTOFF = dbo.[UFN_PURGEGLOBALCHANGE_RESOLVEDATEFILTER](@DATEPART, @DATEINTERVAL, getdate());

            if @CUTOFF is null
                raiserror('BBERR_COULDNOTRESOLVEINTERVAL', 13, 1);
        end

        create table #STATUSES ([BUSINESSPROCESSSTATUSID] uniqueidentifier, [STATUSDATE] datetime);

        if @SELECTIONID is null
            if @NUMBERTOKEEP > 0 begin

                insert into #STATUSES
                    select [BUSINESSPROCESSSTATUSID], [STATUSDATE] from
                        (select [BUSINESSPROCESSSTATUS].[ID] as [BUSINESSPROCESSSTATUSID], row_number() over(partition by BUSINESSPROCESSCATALOGID, BUSINESSPROCESSPARAMETERSETID order by [ENDEDON] desc) as [ROWNUMBER], [STARTEDON] as [STATUSDATE]
                        from dbo.[BUSINESSPROCESSSTATUS]
                        where ENDEDON is not null) [STATUS]
                    where [ROWNUMBER] > @NUMBERTOKEEP;
            end
            else begin
                insert into #STATUSES
                    select [BUSINESSPROCESSSTATUS].[ID] as [BUSINESSPROCESSSTATUSID], [STARTEDON] as [STATUSDATE]
                    from dbo.[BUSINESSPROCESSSTATUS]
                    where [BUSINESSPROCESSSTATUS].[ENDEDON] < @CUTOFF; 
        end
        else 
            if @NUMBERTOKEEP > 0 begin
                insert into #STATUSES
                    select [BUSINESSPROCESSSTATUSID], [STATUSDATE] from
                        (select [BUSINESSPROCESSSTATUS].[ID] as [BUSINESSPROCESSSTATUSID], row_number() over(partition by BUSINESSPROCESSCATALOGID, BUSINESSPROCESSPARAMETERSETID order by [ENDEDON] desc) as [ROWNUMBER], [STARTEDON] as [STATUSDATE]
                        from dbo.[BUSINESSPROCESSSTATUS]
                        where ENDEDON is not null and [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSCATALOGID] in
                        (select [ID] from dbo.[UFN_IDSETREADER_GETRESULTS_GUID](@SELECTIONID))) [STATUS]
                    where [ROWNUMBER] > @NUMBERTOKEEP;
            end
            else begin
                insert into #STATUSES
                    select [BUSINESSPROCESSSTATUS].[ID] as [BUSINESSPROCESSSTATUSID], [STARTEDON] as [STATUSDATE]
                    from dbo.[BUSINESSPROCESSSTATUS]
                    where [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSCATALOGID] in
                    (select [ID] from dbo.[UFN_IDSETREADER_GETRESULTS_GUID](@SELECTIONID))
                    and ENDEDON is not null
                    and [BUSINESSPROCESSSTATUS].[ENDEDON] < @CUTOFF;
            end

        -- Remove business processes from temp table for "Receipting Process" business process which are not allowed to delete from UI. The receipt process with Receipt date  are not allowed to delete from UI

        delete #STATUSES
        from dbo.[REVENUERECEIPT] 
        inner join #STATUSES on RECEIPTINGPROCESSSTATUSID = [BUSINESSPROCESSSTATUSID] and RECEIPTDATE is not null;

        select @FILTERDATE = min([STATUSDATE]), @MAXDATE = max([STATUSDATE]) from #STATUSES;
        set @PREVIOUSFILTERDATE = @FILTERDATE;

        while (@FILTERDATE < @MAXDATE)
        begin
            set @FILTERDATE = dateadd(month, 1, @FILTERDATE);

            -- Update dependent table for "Invitation Process - with custom formatting rules" business process

            update INVITATION
            set SEGMENTATIONACTIVATEPROCESSSTATUSID = null
            from dbo.[INVITATIONHISTORY] INVITATION
            join #STATUSES [STATUS] on INVITATION.SEGMENTATIONACTIVATEPROCESSSTATUSID = [STATUS].BUSINESSPROCESSSTATUSID
            where STATUSDATE <= @FILTERDATE and STATUSDATE >= @PREVIOUSFILTERDATE;

            delete BUSINESSPROCESSSTATUS
            from dbo.[BUSINESSPROCESSSTATUS]
            join #STATUSES on ID = BUSINESSPROCESSSTATUSID
            where STATUSDATE <= @FILTERDATE and STATUSDATE >= @PREVIOUSFILTERDATE;

            set @PREVIOUSFILTERDATE = @FILTERDATE;
        end

        select @NUMBERDELETED = count([BUSINESSPROCESSSTATUSID]) from #STATUSES;
        drop table #STATUSES;
    end try
    begin catch
        exec dbo.[USP_RAISE_ERROR];
        return 1;
    end catch

    return 0;
end