USP_DATALIST_FUNDRAISINGPURPOSEHIERARCHIES

Returns the designation hierarchies linked to a given purpose.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DESIGNATIONLEVELID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DESIGNATIONREPORT1CODEID	uniqueidentifier	IN	Report code 1
@DESIGNATIONREPORT2CODEID	uniqueidentifier	IN	Report code 2

Definition

Copy



CREATE procedure dbo.USP_DATALIST_FUNDRAISINGPURPOSEHIERARCHIES (
  @CURRENTAPPUSERID uniqueidentifier
  ,@DESIGNATIONLEVELID uniqueidentifier
  ,@DESIGNATIONREPORT1CODEID uniqueidentifier = null
  ,@DESIGNATIONREPORT2CODEID uniqueidentifier = null
  )
as
set nocount on;

select ID
  ,[NAME]
  ,DESIGNATIONREPORT1CODEID
  ,(
    select DESCRIPTION
    from dbo.DESIGNATIONREPORT1CODE
    where ID = DESIGNATIONREPORT1CODEID
    )
  ,DESIGNATIONREPORT2CODEID
  ,(
    select DESCRIPTION
    from dbo.DESIGNATIONREPORT2CODE
    where ID = DESIGNATIONREPORT2CODEID
    )
  ,ISACTIVE
  ,case 
    when DESIGNATION.DESIGNATIONLEVEL2ID is null
      then 1
    when DESIGNATION.DESIGNATIONLEVEL3ID is null
      then 2
    when DESIGNATION.DESIGNATIONLEVEL4ID is null
      then 3
    when DESIGNATION.DESIGNATIONLEVEL5ID is null
      then 4
    else 5
    end as [LEVEL]
  ,DESIGNATION.SYSTEMGENERATED
  ,coalesce(DESIGNATION.DESIGNATIONLEVEL5ID, DESIGNATION.DESIGNATIONLEVEL4ID, DESIGNATION.DESIGNATIONLEVEL3ID, DESIGNATION.DESIGNATIONLEVEL2ID, DESIGNATION.DESIGNATIONLEVEL1ID) [DESIGNATIONLEVELID]
from dbo.DESIGNATION
where (
    DESIGNATION.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
    or DESIGNATION.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID
    or DESIGNATION.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID
    or DESIGNATION.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID
    or DESIGNATION.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID
    )
  and (DESIGNATION.ISREVENUEDESIGNATION = 0)
  and (
    (@DESIGNATIONREPORT1CODEID is null)
    or (DESIGNATION.DESIGNATIONREPORT1CODEID = @DESIGNATIONREPORT1CODEID)
    )
  and (
    (@DESIGNATIONREPORT2CODEID is null)
    or (DESIGNATION.DESIGNATIONREPORT2CODEID = @DESIGNATIONREPORT2CODEID)
    )
  and (dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(DESIGNATION.ID)) = 1);