USP_DATALIST_LEDGERACCOUNT

Returns a list of defined ledger accounts.
Parameters

Parameter	Parameter Type	Mode	Description
@DESCRIPTION	nvarchar(400)	IN	Description
@CATEGORY	tinyint	IN	Category
@VIEWONLYINACTIVE	bit	IN	View only accounts with inactive segments
@INCLUDEINACTIVE	bit	IN	Include inactive
@MAXNUMROWS	int	IN	Limit
@PDACCOUNTSYSTEMID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


CREATE procedure dbo.USP_DATALIST_LEDGERACCOUNT (
  @DESCRIPTION nvarchar(400) = null,
  @CATEGORY tinyint = null,
  @VIEWONLYINACTIVE bit = 0,
    @INCLUDEINACTIVE bit = 0,
  @MAXNUMROWS int = 100,
  @PDACCOUNTSYSTEMID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier
    )
as
    set nocount on;

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

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

  if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
      select top (@MAXNUMROWS)
          GLACCOUNT.ID ID,
          GLACCOUNT.ACCOUNTSTRING ACCOUNT,
          GLACCOUNT.ACCOUNTDESCRIPTION DESCRIPTION,
          ACCOUNTCODE.CATEGORY CATEGORY,
          ACCOUNTCODE.SUBCATEGORY SUBCATEGORY,
      case INACTIVELEMENTS.INACTIVECOUNT when 0 then '' else 'RES:warning' end as IMAGEKEY,
          GLACCOUNT.ACTIVE
      from 
          dbo.GLACCOUNT
      cross apply
      (select COUNT(1) INACTIVECOUNT
       from 
        (select ID, 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.GLACCOUNT l
           where l.ID = GLACCOUNT.ID
        ) as 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
        ) ACCOUNTELEMENTS
        inner join dbo.PDACCOUNTSEGMENTVALUE on ACCOUNTELEMENTS.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
        where PDACCOUNTSEGMENTVALUE.ISACTIVE = 0
      ) INACTIVELEMENTS
      inner join dbo.PDACCOUNTSYSTEM on GLACCOUNT.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
          left join dbo.ACCOUNTCODE on ACCOUNTCODEID = ACCOUNTCODE.ID
      left join dbo.PDACCOUNTSEGMENTVALUE on GLACCOUNT.ACCOUNTCODEID = PDACCOUNTSEGMENTVALUE.ID
      where
      GLACCOUNT.ACCOUNTDESCRIPTION like isnull(@DESCRIPTION + '%', '%') 
      and GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and PDACCOUNTSYSTEM.ISBASICGL = 0
      and ((@CATEGORY is null) or (ACCOUNTCODE.CATEGORYCODE = @CATEGORY))
      and (INACTIVELEMENTS.INACTIVECOUNT > 0 or @VIEWONLYINACTIVE = 0)
          and (GLACCOUNT.ACTIVE = 1 or @INCLUDEINACTIVE = 1)
      order by
          ACCOUNTSTRING
          ;
  else
  begin
    declare @STCOUNT int
    select @STCOUNT = COUNT(ID) from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ELEMENTTYPECODE=1
    declare @T table (ACCOUNTID uniqueidentifier primary key, ELEMENTCOUNT int)
    insert @T (ACCOUNTID, ELEMENTCOUNT)
    select ID, COUNT(DATAELEMENTID) ELEMENTCOUNT
    from
      (select ID, 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.GLACCOUNT l
        ) as 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
        inner join dbo.UFN_ACCOUNTINGELEMENT_ELEMENTSFORUSER(@CURRENTAPPUSERID) F1 on unPvt.DATAELEMENTID = F1.ELEMENTID
        ) ACCOUNTELEMENTS
    group by ID
    having COUNT(DATAELEMENTID) = @STCOUNT

      select top (@MAXNUMROWS)
          GLACCOUNT.ID ID,
          GLACCOUNT.ACCOUNTSTRING ACCOUNT,
          GLACCOUNT.ACCOUNTDESCRIPTION DESCRIPTION,
          ACCOUNTCODE.CATEGORY CATEGORY,
          ACCOUNTCODE.SUBCATEGORY SUBCATEGORY,
      case INACTIVELEMENTS.INACTIVECOUNT when 0 then '' else 'RES:warning' end as IMAGEKEY,
          GLACCOUNT.ACTIVE
      from 
          dbo.GLACCOUNT
      cross apply
      (select COUNT(1) INACTIVECOUNT
       from 
        (select ID, 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.GLACCOUNT l
           where l.ID = GLACCOUNT.ID
        ) as 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
        ) ACCOUNTELEMENTS
        inner join dbo.PDACCOUNTSEGMENTVALUE on ACCOUNTELEMENTS.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
        where PDACCOUNTSEGMENTVALUE.ISACTIVE = 0
      ) INACTIVELEMENTS
      inner join dbo.PDACCOUNTSYSTEM on GLACCOUNT.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
          left join dbo.ACCOUNTCODE on ACCOUNTCODEID = ACCOUNTCODE.ID
      left join dbo.PDACCOUNTSEGMENTVALUE on GLACCOUNT.ACCOUNTCODEID = PDACCOUNTSEGMENTVALUE.ID
      where
      GLACCOUNT.ACCOUNTDESCRIPTION like isnull(@DESCRIPTION + '%', '%') 
      and GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and PDACCOUNTSYSTEM.ISBASICGL = 0
      and ((@CATEGORY is null) or (ACCOUNTCODE.CATEGORYCODE = @CATEGORY))
      and (INACTIVELEMENTS.INACTIVECOUNT > 0 or @VIEWONLYINACTIVE = 0)
          and (GLACCOUNT.ACTIVE = 1 or @INCLUDEINACTIVE = 1)
          and GLACCOUNT.ID in (select ACCOUNTID from @T)
      order by
          ACCOUNTSTRING
          ;
  end