USP_DATALIST_GL_ACCOUNTCODE

CREATE procedure dbo.USP_DATALIST_GL_ACCOUNTCODE(
  @ACCOUNTCODE nvarchar(100) = null,
  @DESCRIPTION nvarchar(60) = null,
  @CATEGORY tinyint = null,
  @SUBCATEGORY tinyint = null,
  @MAXNUMROWS int = 100,
  @INCLUDEINACTIVE bit = 0,
  @PDACCOUNTSYSTEMID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier
  )
as
begin
  set nocount on;

  if not @ACCOUNTCODE is null
    Set @ACCOUNTCODE = replace(ltrim(rtrim(@ACCOUNTCODE)),'*','%');

  if not @DESCRIPTION is null
    set @DESCRIPTION = replace(ltrim(rtrim(@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 where ACCOUNTTYPECODE <> 2) 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

  if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
    select top (@MAXNUMROWS)
      ACCOUNTCODE.ID,
      PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION as [ACCOUNTCODE],
      PDACCOUNTSEGMENTVALUE.DESCRIPTION,
      ACCOUNTCODE.CATEGORY as CATEGORY,
      ACCOUNTCODE.SUBCATEGORY as SUBCATEGORY,
      ACCOUNTCODE.CONTRAACCOUNT,
      case when PDACCOUNTSEGMENTVALUE.ID in (select DATAELEMENTID from @DATAELEMENTS) then 1 else 0 end as CONTROLACCOUNT,
      PDACCOUNTSEGMENTVALUE.ISACTIVE as [ACTIVE],
      PDACCOUNTSTRUCTURE.DESCRIPTION as [ELEMENTNAME]
    from dbo.ACCOUNTCODE 
      inner join dbo.PDACCOUNTSEGMENTVALUE on ACCOUNTCODE.ID = PDACCOUNTSEGMENTVALUE.ID
      inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
    where PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION like isnull(@ACCOUNTCODE + '%', '%')
      and PDACCOUNTSEGMENTVALUE.DESCRIPTION like isnull(@DESCRIPTION + '%', '%') 
      and ((@CATEGORY is null) or (CATEGORYCODE = @CATEGORY)) 
      and ((@SUBCATEGORY is null) or (SUBCATEGORYCODE = @SUBCATEGORY))
      and (@INCLUDEINACTIVE = 1 or PDACCOUNTSEGMENTVALUE.ISACTIVE = 1)
    order by
      PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION asc;
  else
    select top (@MAXNUMROWS)
      ACCOUNTCODE.ID,
      PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION as [ACCOUNTCODE],
      PDACCOUNTSEGMENTVALUE.DESCRIPTION,
      ACCOUNTCODE.CATEGORY as CATEGORY,
      ACCOUNTCODE.SUBCATEGORY as SUBCATEGORY,
      ACCOUNTCODE.CONTRAACCOUNT,
      case when PDACCOUNTSEGMENTVALUE.ID in (select DATAELEMENTID from @DATAELEMENTS) then 1 else 0 end as CONTROLACCOUNT,
      PDACCOUNTSEGMENTVALUE.ISACTIVE as [ACTIVE],
      PDACCOUNTSTRUCTURE.DESCRIPTION as [ELEMENTNAME]
    from dbo.ACCOUNTCODE 
      inner join dbo.PDACCOUNTSEGMENTVALUE on ACCOUNTCODE.ID = PDACCOUNTSEGMENTVALUE.ID
      inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
    where PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION like isnull(@ACCOUNTCODE + '%', '%')
      and PDACCOUNTSEGMENTVALUE.DESCRIPTION like isnull(@DESCRIPTION + '%', '%') 
      and ((@CATEGORY is null) or (CATEGORYCODE = @CATEGORY)) 
      and ((@SUBCATEGORY is null) or (SUBCATEGORYCODE = @SUBCATEGORY))
      and (@INCLUDEINACTIVE = 1 or PDACCOUNTSEGMENTVALUE.ISACTIVE = 1)
      and PDACCOUNTSEGMENTVALUE.ID in (select ELEMENTID from dbo.UFN_ACCOUNTINGELEMENT_ELEMENTSFORUSER(@CURRENTAPPUSERID))
    order by
      PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION asc;
end