USP_SEARCHLIST_LEDGERACCOUNT

CREATE procedure dbo.USP_SEARCHLIST_LEDGERACCOUNT
(
  @DATAELEMENTS xml = null,
  @DESCRIPTION nvarchar(400) = '',
  @CATEGORYCODE tinyint = null,
  @SUBCATEGORYCODE tinyint = null,
  @ISCONTRA bit = null,
  @INCLUDEINACTIVE bit = 0,
  @USERACCOUNTSTRING nvarchar(130) = null,
  @MAXROWS smallint = 500
)
as
  set @DESCRIPTION = coalesce(replace(@DESCRIPTION,'*','%'),'') + '%';
  set @USERACCOUNTSTRING = coalesce(replace(@USERACCOUNTSTRING,'*','%'),'') + '%';
  declare @SEPARATOR nvarchar(1)
  select top 1 @SEPARATOR = SEPARATOR from dbo.PDACCOUNTSTRUCTURE
  declare @ACCOUNTSTRING nvarchar(130)
  set @ACCOUNTSTRING = 
    (select
      coalesce(case when T1.DATAELEMENTID is null then '%'+@SEPARATOR else SHORTDESCRIPTION+@SEPARATOR end, '')
    from NUMBERS          
    inner join PDACCOUNTSTRUCTURE on NUMBERS.NUM = PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE
    left join dbo.UFN_LEDGERACCOUNT_GETACCOUNTSEGMENTS_FROMITEMLISTXML(@DATAELEMENTS) T1 on PDACCOUNTSTRUCTURE.ID = T1.ACCOUNTSTRUCTUREID
    left join PDACCOUNTSEGMENTVALUE on T1.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
    where PDACCOUNTSTRUCTURE.ELEMENTTYPECODE = 1
    order by PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE
    for xml path(''), type).value('.','nvarchar(130)')
  set @ACCOUNTSTRING = substring(@ACCOUNTSTRING, 1, len(@ACCOUNTSTRING)-1)

  select top(@MAXROWS)
    GLACCOUNT.ID,
    --dbo.UFN_LEDGERACCOUNT_GETACCOUNTSTRING(GLACCOUNT.ID) as ACCOUNTSTRING,

    GLACCOUNT.ACCOUNTSTRING as ACCOUNTSTRING,
    GLACCOUNT.ACCOUNTDESCRIPTION,
    ACCOUNTCODE.CATEGORY AS CATEGORY,
    ACCOUNTCODE.SUBCATEGORY AS SUBCATEGORY
  from 
    dbo.GLACCOUNT
    left join dbo.ACCOUNTCODE on GLACCOUNT.ACCOUNTCODEID = ACCOUNTCODE.ID
  where
    (GLACCOUNT.ACCOUNTSTRING like @ACCOUNTSTRING)
  and
      (GLACCOUNT.ACCOUNTDESCRIPTION like @DESCRIPTION)
  and
    (@CATEGORYCODE is null or CATEGORYCODE = @CATEGORYCODE)
  and
    (@SUBCATEGORYCODE is null or SUBCATEGORYCODE = @SUBCATEGORYCODE)
  and
    (@ISCONTRA is null or CONTRAACCOUNT = @ISCONTRA)
  and
    (ACTIVE = 1 or @INCLUDEINACTIVE = 1)
  and
    ((@USERACCOUNTSTRING is null) or (GLACCOUNT.ACCOUNTSTRING like @USERACCOUNTSTRING)) 
  order by
    ACCOUNTSTRING;