UFN_CONSTITUENTRECOGNITION_GETDECLINEDLEVELS

      CREATE function dbo.UFN_CONSTITUENTRECOGNITION_GETDECLINEDLEVELS
      (
        @CONSTITUENTID uniqueidentifier,
        @RECOGNITIONPROGRAMID uniqueidentifier
      )
      returns table
      as
      return 
      (

    select case when (CRDL.ID is not null)  then 1
       else 0
       end as DECLINED,
       CRDL.ID AS DECLINEDLEVELID,
       RL.ID as RECOGNITIONLEVELID,
     RL.NAME as RECOGNITIONLEVELNAME,
     CRDP.ID as DECLINEDPROGRAM
    from dbo.RECOGNITIONLEVEL RL
    left join dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM CRDP on CRDP.CONSTITUENTID = @CONSTITUENTID and CRDP.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
    left join dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL CRDL on CRDL.CONSTITUENTID=@CONSTITUENTID and CRDL.RECOGNITIONLEVELID = RL.ID and CRDL.RECOGNITIONPROGRAMID =  @RECOGNITIONPROGRAMID
    where RL.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID 
       )