USP_DATALIST_PROJECT

This data list returns all projects.
Parameters

Parameter	Parameter Type	Mode	Description
@PROJECTID	nvarchar(100)	IN	Project ID
@DESCRIPTION	nvarchar(60)	IN	Description
@INCLUDEINACTIVE	bit	IN	Include inactive
@MAXNUMROWS	int	IN	Limit
@PDACCOUNTSYSTEMID	uniqueidentifier	IN	PD account system ID
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


CREATE procedure dbo.USP_DATALIST_PROJECT(
    @PROJECTID nvarchar(100) = null, 
    @DESCRIPTION nvarchar(60) = null,
    @INCLUDEINACTIVE bit = 0,
  @MAXNUMROWS int = 100,
  @PDACCOUNTSYSTEMID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier
    )
as
    set nocount on;

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

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

  if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
      select top (@MAXNUMROWS)
          PROJECT.ID,
          PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION as [PROJECTID],
          PDACCOUNTSEGMENTVALUE.DESCRIPTION as [DESCRIPTION],
          PROJECTTYPECODE.DESCRIPTION as [TYPE],
          PROJECTLOCATIONCODE.DESCRIPTION as [LOCATION],
          PROJECTDIVISIONCODE.DESCRIPTION as [DIVISION],
          PROJECTDEPARTMENTCODE.DESCRIPTION as [DEPARTMENT],
          PROJECT.STARTDATE,
          PROJECT.ENDDATE,
          PDACCOUNTSEGMENTVALUE.ISACTIVE as [ACTIVE],
      PDACCOUNTSTRUCTURE.DESCRIPTION as [ELEMENTNAME]
      from
          dbo.PROJECT
          inner join dbo.PDACCOUNTSEGMENTVALUE on PDACCOUNTSEGMENTVALUE.ID = PROJECT.ID
      inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
          left join dbo.PROJECTTYPECODE on PROJECTTYPECODE.ID = PROJECT.PROJECTTYPECODEID
          left join dbo.PROJECTLOCATIONCODE on PROJECTLOCATIONCODE.ID = PROJECT.PROJECTLOCATIONCODEID
          left join dbo.PROJECTDIVISIONCODE on PROJECTDIVISIONCODE.ID = PROJECT.PROJECTDIVISIONCODEID
          left join dbo.PROJECTDEPARTMENTCODE on PROJECTDEPARTMENTCODE.ID = PROJECT.PROJECTDEPARTMENTCODEID
      where
          ((PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION like @PROJECTID + '%') or (@PROJECTID is null))
          and ((PDACCOUNTSEGMENTVALUE.DESCRIPTION like @DESCRIPTION + '%') or (@DESCRIPTION is null))
          and (@INCLUDEINACTIVE = 1 or PDACCOUNTSEGMENTVALUE.ISACTIVE = 1)
      and PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and PDACCOUNTSTRUCTURE.ISBASICGL = 0
      order by
          PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION
    else
      select top (@MAXNUMROWS)
          PROJECT.ID,
          PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION as [PROJECTID],
          PDACCOUNTSEGMENTVALUE.DESCRIPTION as [DESCRIPTION],
          PROJECTTYPECODE.DESCRIPTION as [TYPE],
          PROJECTLOCATIONCODE.DESCRIPTION as [LOCATION],
          PROJECTDIVISIONCODE.DESCRIPTION as [DIVISION],
          PROJECTDEPARTMENTCODE.DESCRIPTION as [DEPARTMENT],
          PROJECT.STARTDATE,
          PROJECT.ENDDATE,
          PDACCOUNTSEGMENTVALUE.ISACTIVE as [ACTIVE],
      PDACCOUNTSTRUCTURE.DESCRIPTION as [ELEMENTNAME]
      from
          dbo.PROJECT
          inner join dbo.PDACCOUNTSEGMENTVALUE on PDACCOUNTSEGMENTVALUE.ID = PROJECT.ID
      inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
          left join dbo.PROJECTTYPECODE on PROJECTTYPECODE.ID = PROJECT.PROJECTTYPECODEID
          left join dbo.PROJECTLOCATIONCODE on PROJECTLOCATIONCODE.ID = PROJECT.PROJECTLOCATIONCODEID
          left join dbo.PROJECTDIVISIONCODE on PROJECTDIVISIONCODE.ID = PROJECT.PROJECTDIVISIONCODEID
          left join dbo.PROJECTDEPARTMENTCODE on PROJECTDEPARTMENTCODE.ID = PROJECT.PROJECTDEPARTMENTCODEID
      where
          ((PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION like @PROJECTID + '%') or (@PROJECTID is null))
          and ((PDACCOUNTSEGMENTVALUE.DESCRIPTION like @DESCRIPTION + '%') or (@DESCRIPTION is null))
          and (@INCLUDEINACTIVE = 1 or PDACCOUNTSEGMENTVALUE.ISACTIVE = 1)
      and PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and PDACCOUNTSTRUCTURE.ISBASICGL = 0
      and PDACCOUNTSEGMENTVALUE.ID in (select ELEMENTID from dbo.UFN_ACCOUNTINGELEMENT_ELEMENTSFORUSER(@CURRENTAPPUSERID))
      order by
          PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION