USP_GLOBALCHANGE_BUSINESSPROCESSSTATUSDELETE

    CREATE procedure [USP_GLOBALCHANGE_BUSINESSPROCESSSTATUSDELETE]
    (
     @CHANGEAGENTID uniqueidentifier = null,
     @ASOF as datetime = null,
     @NUMBERADDED integer = 0 output,
     @NUMBEREDITED integer = 0 output,
     @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

    )
    with execute as owner
    as begin
    set nocount on;

    declare @CUTOFF datetime;
    declare @SQL nvarchar(max); 
    declare @FILTERDATE datetime;
    declare @PREVIOUSFILTERDATE datetime;
    declare @MAXDATE datetime;
    declare @STATUSID uniqueidentifier;
    declare @SEGMENTATIONID uniqueidentifier; 
    declare @SEGMENTATIONEXCLUSIONID uniqueidentifier;   
    declare @STEWARDSHIPPACKAGEID uniqueidentifier;

    set @NUMBERADDED = 0;
    set @NUMBEREDITED = 0;
    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
    end try
    begin catch
        exec dbo.[USP_RAISE_ERROR];
        return 1;
    end catch

    begin try
        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(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(order by [ENDEDON] desc) as [ROWNUMBER], [STARTEDON] as [STATUSDATE] 
                        from dbo.[BUSINESSPROCESSSTATUS] 
                        where ENDEDON is not null and [BUSINESSPROCESSSTATUS].[ID] 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].[ID] 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;

        -- Delete dependent table for "Receipting Process" business process

        -- ReceiptingProcessStatusDelete.RecordOperation.xml

        declare RECEIPTSTATUS cursor local fast_forward for
            select distinct [BUSINESSPROCESSSTATUSID]
            from dbo.[REVENUERECEIPT]
            inner join #STATUSES on RECEIPTINGPROCESSSTATUSID = BUSINESSPROCESSSTATUSID;

        open RECEIPTSTATUS;
        fetch next from RECEIPTSTATUS into @STATUSID;

        while (@@FETCH_STATUS = 0)
        begin
            exec dbo.USP_RECEIPTINGPROCESSSTATUS_DELETE @STATUSID, @CHANGEAGENTID;
            fetch next from RECEIPTSTATUS into @STATUSID;
        end

        close RECEIPTSTATUS;
        deallocate RECEIPTSTATUS;

        -- Delete dependent table for "Remove Members Process" business process

        -- SegmentationExclusionDelete.RecordOperation.xml

        declare SEGMENTPROCESS cursor local fast_forward for
            select SEGMENTATIONID, ID
            from dbo.[MKTSEGMENTATIONEXCLUSION]
            inner join #STATUSES on STATUSID = [BUSINESSPROCESSSTATUSID];

        open SEGMENTPROCESS;
        fetch next from SEGMENTPROCESS into @SEGMENTATIONID, @SEGMENTATIONEXCLUSIONID;

        while (@@FETCH_STATUS = 0)
        begin
            exec dbo.USP_MKTSEGMENTATIONACTIVATE_MERGECONSTITUENTS @SEGMENTATIONID, @SEGMENTATIONEXCLUSIONID;
            exec dbo.USP_MKTSEGMENTATIONEXCLUSION_ROLLBACK @SEGMENTATIONEXCLUSIONID, @CHANGEAGENTID;
            fetch next from SEGMENTPROCESS into @SEGMENTATIONID, @SEGMENTATIONEXCLUSIONID;
        end

        close SEGMENTPROCESS;
        deallocate SEGMENTPROCESS;

        -- Delete dependent table for "Acknowledgement Process" business process

        -- AcknowledgementProcessStatusDelete.RecordOperation.xml

        declare ACKNOWLEDGMENTPROCESS cursor local fast_forward for
            select distinct [BUSINESSPROCESSSTATUSID]
            from dbo.[REVENUELETTER]
            inner join #STATUSES on ACKNOWLEDGEMENTPROCESSSTATUSID = BUSINESSPROCESSSTATUSID
            and ACKNOWLEDGEDATE is null;

        open ACKNOWLEDGMENTPROCESS;
        fetch next from ACKNOWLEDGMENTPROCESS into @STATUSID;

        while (@@FETCH_STATUS = 0)
        begin
            exec dbo.USP_ACKNOWLEDGEMENTPROCESSSTATUS_DELETE @STATUSID, @CHANGEAGENTID;
            fetch next from ACKNOWLEDGMENTPROCESS into @STATUSID;
        end

        close ACKNOWLEDGMENTPROCESS;
        deallocate ACKNOWLEDGMENTPROCESS;

        -- Delete dependent table for "Planned Gift Acknowledgement Process" business process

        -- PlannedGiftAcknowledgementProcessStatusDelete.RecordOperation.xml

        declare PLANNEDGIFTPROCESSS cursor local fast_forward for
            select distinct [BUSINESSPROCESSSTATUSID]
            from dbo.[PLANNEDGIFTLETTER]
            inner join #STATUSES on PLANNEDGIFTACKNOWLEDGEMENTPROCESSSTATUSID = BUSINESSPROCESSSTATUSID
            and ACKNOWLEDGEDATE is null;

        open PLANNEDGIFTPROCESSS;
        fetch next from PLANNEDGIFTPROCESSS into @STATUSID;

        while (@@FETCH_STATUS = 0)
        begin
            exec dbo.USP_PLANNEDGIFTACKNOWLEDGEMENTPROCESSSTATUS_DELETE @STATUSID, @CHANGEAGENTID;
            fetch next from PLANNEDGIFTPROCESSS into @STATUSID;
        end

        close PLANNEDGIFTPROCESSS;
        deallocate PLANNEDGIFTPROCESSS;


        -- Delete dependent table for "Generate Reminders Process" business process

        -- PledgeReminderProcessStatusDelete.RecordOperation.xml

        declare PLEDGEREMINDERPROCESSS cursor local fast_forward for
            select distinct [BUSINESSPROCESSSTATUSID]
            from dbo.[PLEDGEREMINDERSENT]
            inner join #STATUSES on PLEDGEREMINDERPROCESSSTATUSID = BUSINESSPROCESSSTATUSID
            and SENTDATE is null;

        open PLEDGEREMINDERPROCESSS;
        fetch next from PLEDGEREMINDERPROCESSS into @STATUSID;

        while (@@FETCH_STATUS = 0)
        begin
            exec dbo.USP_PLEDGEREMINDERPROCESSSTATUS_DELETE @STATUSID, @CHANGEAGENTID;
            fetch next from PLEDGEREMINDERPROCESSS into @STATUSID;
        end

        close PLEDGEREMINDERPROCESSS;
        deallocate PLEDGEREMINDERPROCESSS;

        -- Delete dependent table for "R68 Process" business process

        -- R68ProcessStatusDelete.RecordOperation.xml

        declare R68PROCESSS cursor local fast_forward for
            select distinct [BUSINESSPROCESSSTATUSID]
            from dbo.[R68PROCESSCOMMITPARAMETERS]
            inner join #STATUSES on ID = BUSINESSPROCESSSTATUSID;

        open R68PROCESSS;
        fetch next from R68PROCESSS into @STATUSID;

        while (@@FETCH_STATUS = 0)
        begin
            exec dbo.USP_R68PROCESSSTATUS_DELETE @STATUSID, @CHANGEAGENTID;
            fetch next from R68PROCESSS into @STATUSID;
        end

        close R68PROCESSS;
        deallocate R68PROCESSS;

        -- Delete dependent table for "Tribute Acknowledgement Process" business process

        -- TributeAcknowledgementProcessStatusDelete.RecordOperation.xml

        declare TRIBUTEACKNOWLEDGEMENTPROCESS cursor local fast_forward for
            select distinct [BUSINESSPROCESSSTATUSID]
            from dbo.[TRIBUTEACKNOWLEDGEMENTPROCESSSTATUS]
            inner join #STATUSES on ID = [BUSINESSPROCESSSTATUSID];

        open TRIBUTEACKNOWLEDGEMENTPROCESS;
        fetch next from TRIBUTEACKNOWLEDGEMENTPROCESS into @STATUSID;

        while (@@FETCH_STATUS = 0)
        begin
            exec dbo.USP_TRIBUTEACKNOWLEDGEMENTPROCESSSTATUS_DELETE @STATUSID, @CHANGEAGENTID;
            fetch next from TRIBUTEACKNOWLEDGEMENTPROCESS into @STATUSID;
        end

        close TRIBUTEACKNOWLEDGEMENTPROCESS;
        deallocate TRIBUTEACKNOWLEDGEMENTPROCESS;

        -- Delete dependent table for "Stewardship Package Process" business process

        -- StewardshipPackageProcessStatusDelete.RecordOperation.xml

        declare STEWARDSHIPPACKAGEPROCESSS cursor local fast_forward for
            select ID
            from dbo.[STEWARDSHIPPACKAGEPROCESSHISTORY]
            inner join #STATUSES STATUSES on BUSINESSPROCESSSTATUSGUID = STATUSES.BUSINESSPROCESSSTATUSID;

        open STEWARDSHIPPACKAGEPROCESSS;
        fetch next from STEWARDSHIPPACKAGEPROCESSS into @STEWARDSHIPPACKAGEID;

        while (@@FETCH_STATUS = 0)
        begin
            exec dbo.USP_STEWARDSHIPPACKAGEPROCESSSTATUS_DELETE @STEWARDSHIPPACKAGEID, @CHANGEAGENTID;
            fetch next from STEWARDSHIPPACKAGEPROCESSS into @STEWARDSHIPPACKAGEID;
        end

        close STEWARDSHIPPACKAGEPROCESSS;
        deallocate STEWARDSHIPPACKAGEPROCESSS;

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

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

            delete BUSINESSPROCESSSTATUS
            from 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
    -- Close and deallocate cursor if they are not closed and deallocated

        if cursor_status('local','RECEIPTSTATUS')>=-1
        begin
            close RECEIPTSTATUS;
            deallocate RECEIPTSTATUS;
        end

        if cursor_status('local','SEGMENTPROCESS')>=-1
        begin
            close SEGMENTPROCESS;
            deallocate SEGMENTPROCESS;
        end

        if cursor_status('local','ACKNOWLEDGMENTPROCESS')>=-1
        begin
            close ACKNOWLEDGMENTPROCESS;
            deallocate ACKNOWLEDGMENTPROCESS;
        end

        if cursor_status('local','PLANNEDGIFTPROCESSS')>=-1
        begin
            close PLANNEDGIFTPROCESSS;
            deallocate PLANNEDGIFTPROCESSS;
        end

        if cursor_status('local','PLEDGEREMINDERPROCESSS')>=-1
        begin
            close PLEDGEREMINDERPROCESSS;
            deallocate PLEDGEREMINDERPROCESSS;
        end

     if cursor_status('local','R68PROCESSS')>=-1
        begin
            close R68PROCESSS;
            deallocate R68PROCESSS;
        end

        if cursor_status('local','TRIBUTEACKNOWLEDGEMENTPROCESS')>=-1
        begin
            close TRIBUTEACKNOWLEDGEMENTPROCESS;
            deallocate TRIBUTEACKNOWLEDGEMENTPROCESS;
        end

        if cursor_status('local','STEWARDSHIPPACKAGEPROCESSS')>=-1
        begin
            close STEWARDSHIPPACKAGEPROCESSS;
            deallocate STEWARDSHIPPACKAGEPROCESSS;
        end

        exec dbo.[USP_RAISE_ERROR];
        return 1;
    end catch

    return 0;
end