USP_DATALIST_ACCOUNTINGELEMENTSECURITYGROUPELEMENT

This datalist displays a list of accounting element security group elements.

Parameters

Parameter	Parameter Type	Mode	Description
@CONTEXTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@ACCOUNTSTRUCTUREID	uniqueidentifier	IN	Accounting element
@SHORTID	nvarchar(100)	IN	ID
@DESCRIPTION	nvarchar(60)	IN	Description
@MAXNUMROWS	int	IN	Limit
@PDACCOUNTSYSTEMID	uniqueidentifier	IN	PD account system ID

Definition

Copy



CREATE procedure dbo.USP_DATALIST_ACCOUNTINGELEMENTSECURITYGROUPELEMENT(
  @CONTEXTID uniqueidentifier,
  @ACCOUNTSTRUCTUREID uniqueidentifier = null,
  @SHORTID nvarchar(100) = null,
  @DESCRIPTION nvarchar(60) = null,
  @MAXNUMROWS int = 100,
  @PDACCOUNTSYSTEMID uniqueidentifier = null)
as
  begin
      set nocount on;

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

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

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

      select  top (@MAXNUMROWS)
      PDACCOUNTSTRUCTURE.ID as ACCOUNTSTRUCTUREID,
      PDACCOUNTSEGMENTVALUE.ID as DATAELEMENTID,
      ACCOUNTINGELEMENTSECURITYGROUP.ID as ACCOUNTINGELEMENTSECURITYGROUPID,
      ACCOUNTINGELEMENTSECURITYGROUPELEMENT.ID as ACCOUNTINGELEMENTSECURITYGROUPELEMENTID,
      PDACCOUNTSTRUCTURE.DESCRIPTION as NAME,
      PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION,
      PDACCOUNTSEGMENTVALUE.DESCRIPTION
    from dbo.ACCOUNTINGELEMENTSECURITYGROUPELEMENT
    inner join dbo.PDACCOUNTSEGMENTVALUE on PDACCOUNTSEGMENTVALUE.ID = ACCOUNTINGELEMENTSECURITYGROUPELEMENT.DATAELEMENTID
    inner join dbo.ACCOUNTINGELEMENTSECURITYGROUP on ACCOUNTINGELEMENTSECURITYGROUPELEMENT.ACCOUNTINGELEMENTSECURITYGROUPID = 
      ACCOUNTINGELEMENTSECURITYGROUP.ID
    inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSTRUCTURE.ID = PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID
      where ACCOUNTINGELEMENTSECURITYGROUP.ID = @CONTEXTID and
          (@ACCOUNTSTRUCTUREID is null or PDACCOUNTSTRUCTURE.ID = @ACCOUNTSTRUCTUREID ) and
          PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION like isnull(@SHORTID + '%', '%') and
          PDACCOUNTSEGMENTVALUE.DESCRIPTION like isnull(@DESCRIPTION + '%', '%') and
          PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and PDACCOUNTSTRUCTURE.ISBASICGL = 0
    order by PDACCOUNTSTRUCTURE.DESCRIPTION,PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION

  end