USP_DATALIST_NEWRECORDOPERATIONS

CREATE procedure dbo.USP_DATALIST_NEWRECORDOPERATIONS
(
    @SHOWCODE smallint = 1,
    @RECORDTYPEID uniqueidentifier = null,
    @INCLUDEALLPRODUCTS bit = 0,
    @DATAWINDOWOPEN datetime = null,
    @DATAWINDOWCLOSE datetime = null
)
as
    set nocount on;

    declare @STARTDATE datetime;
    declare @ENDDATE datetime;

    exec dbo.USP_APPCATALOG_GETNEWFEATURESTARTENDDATES @SHOWCODE, @STARTDATE output, @ENDDATE output;

    with xmlnamespaces ('bb_appfx_recordoperation' as tns, 'bb_appfx_commontypes' as common)
    select R.ID,
           R.UINAME as NAME,
           R.DESCRIPTION,
           R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/@Author)[1]', 'nvarchar(500)'),
           coalesce(RT.NAME, ''),
           R.DATEADDED,
           dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(R.RECORDOPERATIONSPECXML.query('tns:RecordOperationSpec/common:InstalledProductList'))
    from dbo.RECORDOPERATIONCATALOG as R
         left join dbo.RECORDTYPE as RT on R.RECORDTYPEID = RT.ID
    where ((@STARTDATE is null) or (R.DATEADDED >= @STARTDATE)) and
          ((@ENDDATE is null) or (R.DATEADDED <= @ENDDATE)) and
          ((@RECORDTYPEID is null) or (R.RECORDTYPEID = @RECORDTYPEID)) and
          ((@INCLUDEALLPRODUCTS = 1) or (1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(R.RECORDOPERATIONSPECXML.query('/*/common:InstalledProductList')))) and
          ((@DATAWINDOWOPEN is null) or (R.DATEADDED >= @DATAWINDOWOPEN)) and 
          ((@DATAWINDOWCLOSE is null) or (R.DATEADDED < @DATAWINDOWCLOSE))
    order by R.DATEADDED DESC