USP_DATALIST_FUNDRAISINGPURPOSEDESIGNATION

Returns the designations 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_FUNDRAISINGPURPOSEDESIGNATION(
                        @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
                        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 = 1
              )
              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
                            );