USP_DATALIST_CONSTITUENTRECOGNITIONDECLINEDLEVELSBYPROGRAM

        CREATE procedure dbo.USP_DATALIST_CONSTITUENTRECOGNITIONDECLINEDLEVELSBYPROGRAM
        (
        @CONSTITUENTID uniqueidentifier
        )
        as
            set nocount on;

          select 
          CRDP.ID,
          '',
          RECOGNITIONPROGRAM.NAME,
          CRDP.DATEADDED,
          RECOGNITIONDECLINEDLEVELREASONCODE.DESCRIPTION as REASONCODE,
          null as RECOGNITIONPROGRAMID
          from dbo.RECOGNITIONPROGRAM
          inner join dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM CRDP on RECOGNITIONPROGRAM.ID = CRDP.RECOGNITIONPROGRAMID and CRDP.CONSTITUENTID = @CONSTITUENTID
          left join dbo.RECOGNITIONDECLINEDLEVELREASONCODE on CRDP.REASONCODEID = RECOGNITIONDECLINEDLEVELREASONCODE.ID

          union all    

          select 
          RECOGNITIONPROGRAM.ID,
          CAST(RECOGNITIONPROGRAM.ID as nvarchar(36)) + CAST(@CONSTITUENTID as nvarchar(36)),
          RECOGNITIONPROGRAM.NAME,
          null,
          '',
          null as RECOGNITIONPROGRAMID
          from dbo.RECOGNITIONPROGRAM
          where RECOGNITIONPROGRAM.ID = (select top 1 CRDL.RECOGNITIONPROGRAMID from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL CRDL where CRDL.CONSTITUENTID = @CONSTITUENTID and CRDL.RECOGNITIONPROGRAMID = RECOGNITIONPROGRAM.ID)

          union all

            select 
             DECLINED.ID,
             '',
             RL.NAME AS NAME,
             DECLINED.DATEADDED,
             RECOGNITIONDECLINEDLEVELREASONCODE.DESCRIPTION as REASONCODE,
             DECLINED.RECOGNITIONPROGRAMID
      from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL DECLINED 
      left join dbo.RECOGNITIONDECLINEDLEVELREASONCODE on DECLINED.REASONCODEID = RECOGNITIONDECLINEDLEVELREASONCODE.ID
      inner join dbo.RECOGNITIONLEVEL RL ON RL.ID = DECLINED.RECOGNITIONLEVELID
      inner join dbo.RECOGNITIONPROGRAM RP on RP.ID = DECLINED.RECOGNITIONPROGRAMID
        where DECLINED.CONSTITUENTID = @CONSTITUENTID