USP_SEARCHLIST_ACCOUNTCODE

CREATE procedure dbo.USP_SEARCHLIST_ACCOUNTCODE
(
  @SHORTID nvarchar(100) = null,
  @DESCRIPTION nvarchar(60) = null,
  @CATEGORYCODE tinyint = null,
  @CATEGORY nvarchar(10) = null,
  @SUBCATEGORYCODE tinyint = null,
  @SUBCATEGORY nvarchar(10) = null,
  @CONTRA tinyint = null,
  @CONTROL tinyint = null,
  @INCLUDEINACTIVE bit = 0,
  @ELEMENTNAME nvarchar(100) = null,
  @MAXROWS smallint = 500,
  @PDACCOUNTSYSTEMID uniqueidentifier = null
)
as
    set @SHORTID = coalesce(replace(@SHORTID,'*','%'),'') + '%' ;
    set @DESCRIPTION = coalesce(replace(@DESCRIPTION,'*','%'),'') + '%';

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

  declare @DATAELEMENTS table
  (DATAELEMENTID uniqueidentifier)

  insert into @DATAELEMENTS(DATAELEMENTID)
  select unPvt.DATAELEMENTID
  from
  (select ID, DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
      DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
      DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID
  from dbo.CONTROLACCOUNT) p
  unpivot
  (DATAELEMENTID for dColumn in
      (DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
      DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
      DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID)) as unPvt

    select top(@MAXROWS)
    PDACCOUNTSEGMENTVALUE.ID,
    PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION,
    PDACCOUNTSEGMENTVALUE.DESCRIPTION,
    ACCOUNTCODE.CATEGORY as CATEGORY,
      ACCOUNTCODE.SUBCATEGORY as SUBCATEGORY,
    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,
    PDACCOUNTSTRUCTURE.DESCRIPTION as [NAME]
    from 
    dbo.PDACCOUNTSEGMENTVALUE inner join dbo.ACCOUNTCODE
      on dbo.PDACCOUNTSEGMENTVALUE.ID = dbo.ACCOUNTCODE.ID
      inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSTRUCTURE.ID = dbo.PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID
  where
     ((PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION like @SHORTID) or (@SHORTID is null)) and (PDACCOUNTSEGMENTVALUE.DESCRIPTION like @DESCRIPTION or @DESCRIPTION is null)
      and
         (@CONTRA is null or CONTRAACCOUNT = @CONTRA)
         and
         (@CONTROL is null or (@CONTROL = 1 and PDACCOUNTSEGMENTVALUE.ID in (select DATAELEMENTID from @DATAELEMENTS)) or (@CONTROL = 0 and PDACCOUNTSEGMENTVALUE.ID not in (select DATAELEMENTID from @DATAELEMENTS)))
     and
         (@CATEGORYCODE is null or CATEGORYCODE = @CATEGORYCODE)
         and
         ((@SUBCATEGORYCODE is null or @SUBCATEGORYCODE = 0) or (SUBCATEGORYCODE = @SUBCATEGORYCODE)) and
         (ISACTIVE = 1 or @INCLUDEINACTIVE = 1)
   order by 
    PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION asc