USP_DATALIST_USERDEFINELEMENT

Returns a list of specific user defined elements.

Parameters

Parameter	Parameter Type	Mode	Description
@GLACCOUNTSTRUCTUREID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@SHORTID	nvarchar(100)	IN	ID
@DESCRIPTION	nvarchar(60)	IN	Description
@INCLUDEINACTIVE	bit	IN	Include inactive
@MAXNUMROWS	int	IN	Limit
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_USERDEFINELEMENT
(
  @GLACCOUNTSTRUCTUREID uniqueidentifier,
  @SHORTID nvarchar(100) = null,
  @DESCRIPTION nvarchar(60) = null,
  @INCLUDEINACTIVE bit = null,
  @MAXNUMROWS int = 100,
  @CURRENTAPPUSERID uniqueidentifier
)
as
  set nocount on;

  set @SHORTID = replace(ltrim(rtrim(@SHORTID)), '*', '%')
  set @DESCRIPTION = replace(ltrim(rtrim(@DESCRIPTION)), '*', '%')

  if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
    select top (@MAXNUMROWS)
      PDACCOUNTSEGMENTVALUE.ID,
      PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION as ELEMENTID,
      PDACCOUNTSEGMENTVALUE.DESCRIPTION,
      PDACCOUNTSEGMENTVALUE.ISACTIVE as ACTIVE,
      PDACCOUNTSEGMENTVALUE.ELEMENTNAME
    from dbo.PDACCOUNTSEGMENTVALUE
      inner join dbo.USERDEFINEDELEMENT on PDACCOUNTSEGMENTVALUE.ID = USERDEFINEDELEMENT.ID
    where PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = @GLACCOUNTSTRUCTUREID
      and ((PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION like @SHORTID + '%') or (@SHORTID is null))
      and ((PDACCOUNTSEGMENTVALUE.DESCRIPTION like @DESCRIPTION + '%') or (@DESCRIPTION is null))
      and ((PDACCOUNTSEGMENTVALUE.ISACTIVE = 1) or (@INCLUDEINACTIVE = 1))
    order by SHORTDESCRIPTION
  else
    -- Only return elements for the user based on record security

    select top (@MAXNUMROWS)
      PDACCOUNTSEGMENTVALUE.ID,
      PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION as ELEMENTID,
      PDACCOUNTSEGMENTVALUE.DESCRIPTION,
      PDACCOUNTSEGMENTVALUE.ISACTIVE as ACTIVE,
      PDACCOUNTSEGMENTVALUE.ELEMENTNAME
    from dbo.PDACCOUNTSEGMENTVALUE
      inner join dbo.USERDEFINEDELEMENT on PDACCOUNTSEGMENTVALUE.ID = USERDEFINEDELEMENT.ID
    where PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = @GLACCOUNTSTRUCTUREID
      and ((PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION like @SHORTID + '%') or (@SHORTID is null))
      and ((PDACCOUNTSEGMENTVALUE.DESCRIPTION like @DESCRIPTION + '%') or (@DESCRIPTION is null))
      and ((PDACCOUNTSEGMENTVALUE.ISACTIVE = 1) or (@INCLUDEINACTIVE = 1))
      and PDACCOUNTSEGMENTVALUE.ID in (select ELEMENTID from dbo.UFN_ACCOUNTINGELEMENT_ELEMENTSFORUSER(@CURRENTAPPUSERID))
    order by SHORTDESCRIPTION