USP_BUSINESSPROCESSSTATUS_GETLIST

CREATE procedure dbo.USP_BUSINESSPROCESSSTATUS_GETLIST
(
    @BUSINESSPROCESSCATALOGID uniqueidentifier = null,
    @SELECTIONID uniqueidentifier = null,
    @DATEPART tinyint = 3,
    @DATEINTERVAL integer = 1,
    @NUMBERTOKEEP integer = 0
)
as
begin
    set nocount on;
    declare @CUTOFF datetime;
    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

        declare @STATUSES table ([BUSINESSPROCESSSTATUSID] uniqueidentifier);

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

        if (@BUSINESSPROCESSCATALOGID = '83048DED-208E-45C9-852C-E7D5C7317882')
        begin
            -- 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;
        end

        if (@BUSINESSPROCESSCATALOGID = 'B4184ABE-C618-466F-90A2-B96B88644A17')
        begin
        --Return ID from STEWARDSHIPPACKAGEPROCESSHISTORY that is expected by StewardshipPackageProcessStatusDelete.RecordOperation.xml as parameter

            select ID
            from dbo.[STEWARDSHIPPACKAGEPROCESSHISTORY]
            inner join @STATUSES STATUSES on BUSINESSPROCESSSTATUSGUID = STATUSES.BUSINESSPROCESSSTATUSID;
        end
        else
        begin
            select BUSINESSPROCESSSTATUSID as ID from @STATUSES
        end
    end try
    begin catch
        exec dbo.[USP_RAISE_ERROR];
    end catch
end