USP_DATALIST_NEWDATAFORMINSTANCES

CREATE procedure dbo.USP_DATALIST_NEWDATAFORMINSTANCES
(
    @SHOWCODE smallint = 1,
    @MODE smallint = null,
    @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_viewdataformtemplate' as viewspec, 'bb_appfx_adddataformtemplate' as addspec, 'bb_appfx_editdataformtemplate' as editspec, 'bb_appfx_commontypes' as common)
    select INSTANCE.ID,
           INSTANCE.UINAME as NAME,
           TEMPLATE.DESCRIPTION,
           case TEMPLATE.MODE
                when 0 then
                    TEMPLATE.TEMPLATESPECXML.value('(viewspec:ViewDataFormTemplateSpec/@Author)[1]', 'nvarchar(500)')
                when 1 then 
                    TEMPLATE.TEMPLATESPECXML.value('(editspec:EditDataFormTemplateSpec/@Author)[1]', 'nvarchar(500)')
                when 2 then
                    TEMPLATE.TEMPLATESPECXML.value('(addspec:AddDataFormTemplateSpec/@Author)[1]', 'nvarchar(500)')
           end,
           coalesce(RT.NAME, ''),
           TEMPLATE.MODE as MODENAME,
           INSTANCE.DATEADDED,
           case TEMPLATE.MODE
               when 0 then
                   dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TEMPLATE.TEMPLATESPECXML.query('viewspec:ViewDataFormTemplateSpec/common:InstalledProductList'))
               when 1 then 
                   dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TEMPLATE.TEMPLATESPECXML.query('editspec:EditDataFormTemplateSpec/common:InstalledProductList'))
               when 2 then
                   dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TEMPLATE.TEMPLATESPECXML.query('addspec:AddDataFormTemplateSpec/common:InstalledProductList'))
           end
    from dbo.DATAFORMINSTANCECATALOG as INSTANCE
         inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
         left join dbo.RECORDTYPE as RT on TEMPLATE.RECORDTYPEID = RT.ID
    where ((@STARTDATE is null) or (INSTANCE.DATEADDED >= @STARTDATE)) and
          ((@ENDDATE is null) or (INSTANCE.DATEADDED <= @ENDDATE)) and
          ((@MODE is null) or (TEMPLATE.MODE = @MODE)) and
          ((@RECORDTYPEID is null) or (TEMPLATE.RECORDTYPEID = @RECORDTYPEID)) and
          ((@INCLUDEALLPRODUCTS = 1) or (1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TEMPLATE.TEMPLATESPECXML.query('/*/common:InstalledProductList')))) and
          ((@DATAWINDOWOPEN is null) or (INSTANCE.DATEADDED >= @DATAWINDOWOPEN)) and 
          ((@DATAWINDOWCLOSE is null) or (INSTANCE.DATEADDED < @DATAWINDOWCLOSE))
    order by INSTANCE.DATEADDED DESC