USP_PROJECT_COMMONDATALIST

CREATE procedure dbo.USP_PROJECT_COMMONDATALIST(
    @PROJECTID nvarchar(100) = null,
    @DESCRIPTION nvarchar(60) = null,
    @INCLUDEINACTIVE bit = 0,
  @MAXNUMROWS int = 100,
  @PDACCOUNTSYSTEMID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier
    )
as
    begin
    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
    end