UFN_RECOGNITIONPROGRAM_GETDESIGNATIONS_ALL

Returns all designations, including child designations of those specified, for the supplied recognition program.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@RECOGNITIONPROGRAMID	uniqueidentifier	IN

Definition

Copy



            create function dbo.UFN_RECOGNITIONPROGRAM_GETDESIGNATIONS_ALL
            (
                @RECOGNITIONPROGRAMID uniqueidentifier
            )
            returns table
            as 
            return
            (
                select D.ID
                from dbo.RECOGNITIONPROGRAMDESIGNATION RPD
                inner join dbo.DESIGNATION D2 on RPD.DESIGNATIONID = D2.ID
                inner join dbo.DESIGNATION D 
                    on D.DESIGNATIONLEVEL1ID = D2.DESIGNATIONLEVEL1ID
                        and (D.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID or D2.DESIGNATIONLEVEL2ID is null)
                        and (D.DESIGNATIONLEVEL3ID = D2.DESIGNATIONLEVEL3ID or D2.DESIGNATIONLEVEL3ID is null)
                        and (D.DESIGNATIONLEVEL4ID = D2.DESIGNATIONLEVEL4ID or D2.DESIGNATIONLEVEL4ID is null)
                        and (D.DESIGNATIONLEVEL5ID = D2.DESIGNATIONLEVEL5ID or D2.DESIGNATIONLEVEL5ID is null)
                where
                    RPD.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
            )