USP_SEARCHLIST_ACCOUNTINGELEMENT

This provides the ability to search for accounting elements.

Parameters

Parameter	Parameter Type	Mode	Description
@SHORTID	nvarchar(100)	IN	ID
@DESCRIPTION	nvarchar(60)	IN	Description
@ACCOUNTSTRUCTUREID	uniqueidentifier	IN	Accounting element
@INCLUDEINACTIVE	bit	IN	Include inactive
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@PDACCOUNTSYSTEMID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_SEARCHLIST_ACCOUNTINGELEMENT
(
    @SHORTID nvarchar(100) = null,
  @DESCRIPTION nvarchar(60) = null,
  @ACCOUNTSTRUCTUREID uniqueidentifier = null,
  @INCLUDEINACTIVE bit = 0,
    @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'

    select top(@MAXROWS)
        PDACCOUNTSEGMENTVALUE.ID,
    PDACCOUNTSTRUCTURE.ID as ACCOUNTSTRUCTUREID,
        PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION,
        PDACCOUNTSEGMENTVALUE.DESCRIPTION,
        PDACCOUNTSTRUCTURE.DESCRIPTION as ELEMENTNAME
    from 
        dbo.PDACCOUNTSEGMENTVALUE 
    inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSTRUCTURE.ID = PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID
  where
         ((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
     (PDACCOUNTSTRUCTURE.ID = @ACCOUNTSTRUCTUREID or @ACCOUNTSTRUCTUREID is null) and
     PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and PDACCOUNTSTRUCTURE.ISBASICGL = 0
    order by 
        PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION asc