USP_DATALIST_COMMITTEDREVENUEBATCHES

CREATE procedure dbo.USP_DATALIST_COMMITTEDREVENUEBATCHES
(                    
  @NUMDAYS int = null
)
as
  set nocount on;

  select
    BATCH.ID,
    BATCH.BATCHNUMBER,
    BATCH.DESCRIPTION,
    BATCH.DATEADDED,
    [TEMPLATE].NAME,
    APPUSER.USERNAME,
    BATCH.STATUS,
    BATCH.STATUSCODE,
    BATCH.PROJECTEDNUMBEROFRECORDS,
    BATCH.PROJECTEDTOTALAMOUNT                
  from dbo.BATCH
  inner join dbo.BATCHTEMPLATE as [TEMPLATE] on [TEMPLATE].ID = BATCH.BATCHTEMPLATEID
  inner join dbo.BATCHTYPECATALOG as [TYPECATALOG] on [TYPECATALOG].ID = [TEMPLATE].BATCHTYPECATALOGID
  inner join dbo.APPUSER on BATCH.APPUSERID = APPUSER.ID
  where 
    upper([TYPECATALOG].CATEGORY) = 'REVENUE' 
    and BATCH.STATUSCODE = 4                    
    and (
      @NUMDAYS is null
      --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

      or (@NUMDAYS is not null and dateadd(ms, -003, dateadd(d, 1, cast(cast(BATCH.DATECOMMITTED as date) as datetime))) > dateadd(dd, -@NUMDAYS, getdate()))
      )
  order by BATCH.BATCHNUMBER;

  return 0;