USP_SEARCHLIST_PROJECT

CREATE procedure dbo.USP_SEARCHLIST_PROJECT
(
    @PROJECTID nvarchar(100) = '',
    @DESCRIPTION nvarchar(60) = '',
    @TYPE uniqueidentifier = null,
    @LOCATION uniqueidentifier = null,
    @DIVISION uniqueidentifier = null,
    @DEPARTMENT uniqueidentifier = null,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @INCLUDEINACTIVE bit = 0,
    @MAXROWS smallint = 500,
  @PDACCOUNTSYSTEMID uniqueidentifier = null
)
as
    set @PROJECTID = coalesce(replace(@PROJECTID,'*','%'),'') + '%' ;
    set @DESCRIPTION = coalesce(replace(@DESCRIPTION,'*','%'),'') + '%';

  if @PDACCOUNTSYSTEMID is null
    set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'

    select top(@MAXROWS)
        PROJECT.ID,
        SHORTDESCRIPTION as PROJECTID,
        PDACCOUNTSEGMENTVALUE.DESCRIPTION,
        dbo.UFN_PROJECTTYPECODE_GETDESCRIPTION(PROJECTTYPECODEID) as TYPE,
        dbo.UFN_PROJECTLOCATIONCODE_GETDESCRIPTION(PROJECTLOCATIONCODEID) as LOCATION,
        dbo.UFN_PROJECTDIVISIONCODE_GETDESCRIPTION(PROJECTDIVISIONCODEID) as DIVISION,
        dbo.UFN_PROJECTDEPARTMENTCODE_GETDESCRIPTION(PROJECTDEPARTMENTCODEID) as DEPARTMENT,
        STARTDATE,
        ENDDATE,
    case PREVENTDATAENTRY when 0 then 'No' else 'Yes' + 
      case when PREVENTDATAENTRYBEFOREDATE is null then 
        case when PREVENTDATAENTRYAFTERDATE is null then ''
        else ': After ' + convert(nvarchar(10), PREVENTDATAENTRYAFTERDATE, 101)
        end
      else ': Before ' + convert(nvarchar(10), PREVENTDATAENTRYBEFOREDATE, 101)
        + coalesce(', After ' + convert(nvarchar(10), PREVENTDATAENTRYAFTERDATE, 101), '')
      end
    end as PREVENTDATAENTRY,
    case PREVENTPOST when 0 then 'No' else 'Yes' +
      case when PREVENTPOSTBEFOREDATE is null then 
        case when PREVENTPOSTAFTERDATE is null then ''
        else ': After ' + convert(nvarchar(10), PREVENTPOSTAFTERDATE, 101)
        end
      else ': Before ' + convert(nvarchar(10), PREVENTPOSTBEFOREDATE, 101)
        + coalesce(', After ' + convert(nvarchar(10), PREVENTPOSTAFTERDATE, 101), '')
      end
    end as PREVENTPOSTING
    from 
        dbo.PDACCOUNTSEGMENTVALUE
        inner join dbo.PROJECT on PDACCOUNTSEGMENTVALUE.ID = PROJECT.ID
    inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
    where
         (SHORTDESCRIPTION LIKE @PROJECTID)
         and
         (PDACCOUNTSEGMENTVALUE.DESCRIPTION like @DESCRIPTION)
         and
         (@TYPE is null or PROJECTTYPECODEID = @TYPE)
         and
         (@LOCATION is null or PROJECTLOCATIONCODEID = @LOCATION)
         and
         (@DIVISION is null or PROJECTDIVISIONCODEID = @DIVISION)
         and
         (@DEPARTMENT is null or PROJECTDEPARTMENTCODEID = @DEPARTMENT)
         and
         (@STARTDATE is null or STARTDATE = @STARTDATE)
         and
         (@ENDDATE is null or ENDDATE = @ENDDATE)
         and
         (ISACTIVE = 1 or @INCLUDEINACTIVE = 1)
     and
     PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and PDACCOUNTSTRUCTURE.ISBASICGL = 0
    order by 
        PROJECTID asc