USP_DATAFORMTEMPLATE_VIEW_PURPOSESUMMARY

create procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PURPOSESUMMARY (
  @ID uniqueidentifier
  ,@DATALOADED bit = 0 output
  ,@NAME nvarchar(100) = null output
  ,@DESCRIPTION nvarchar(255) = null output
  ,@DESIGNATIONLEVELCATEGORY nvarchar(100) = null output
  ,@DESIGNATIONLEVELNAME nvarchar(100) = null output
  ,@HASADMINISTRATOR bit = null output
  ,@HASRECIPIENTS bit = null output
  ,@ADMINISTRATOR nvarchar(700) = null output
  ,@DESIGNATIONID uniqueidentifier = null output
  ,@LOOKUPID nvarchar(100) = null output
  ,@DESIGNATIONREPORTCODE1 nvarchar(100) = null output
  ,@DESIGNATIONREPORTCODE2 nvarchar(100) = null output
  ,@SITE nvarchar(1024) = null output
  ,@DESIGNATIONREPORTCODE1ID uniqueidentifier = null output
  ,@DESIGNATIONREPORTCODE2ID uniqueidentifier = null output
  ,@STEWARDSHIPPACKAGEPROCESSID uniqueidentifier = null output
  ,@STEWARDSHIPPACKAGENAME nvarchar(100) = null output
  ,@VANITYNAME nvarchar(512) = null output
  ,@ENDOWMENTMINAMOUNT money = null output
  ,@ENDOWMENTTARGETDATE date = null output
  ,@ENDOWMENTMINAMOUNTDATEMET date = null output
  ,@ISFULLYFUNDED bit = null output
  ,@ISENDOWED bit = null output
  ,@ADMINISTRATORID uniqueidentifier = null output
  ,@ISACCEPTINGFUNDS bit = null output
  ,@ISDISBURSINGMONEY bit = null output
  ,@BASECURRENCYID uniqueidentifier = null output
  )
as
begin
  set nocount on;
  set @DATALOADED = 0;

  select @DATALOADED = 1
    ,@NAME = DESIGNATIONLEVEL.name
    ,@DESCRIPTION = DESIGNATIONLEVEL.DESCRIPTION
    ,@DESIGNATIONLEVELCATEGORY = CATEGORY.DESCRIPTION
    ,@DESIGNATIONLEVELNAME = FPT.DESCRIPTION
    ,@HASADMINISTRATOR = FPT.HASADMINISTRATOR
    ,@HASRECIPIENTS = FPT.HASRECIPIENTS
    ,@ADMINISTRATOR = NF.name
    ,@DESIGNATIONID = (
      select ID
      from dbo.DESIGNATION
      where DESIGNATIONLEVEL1ID = @ID
        and DESIGNATIONLEVEL2ID is null
      )
    ,@LOOKUPID = DESIGNATIONLEVEL.USERID
    ,@DESIGNATIONREPORTCODE1 = DR1.DESCRIPTION
    ,@DESIGNATIONREPORTCODE2 = DR2.DESCRIPTION
    ,@SITE = dbo.UFN_TRANSLATIONFUNCTION_SITE_GETNAME(DESIGNATIONLEVEL.SITEID)
    ,@DESIGNATIONREPORTCODE1ID = DESIGNATIONLEVEL.DESIGNATIONREPORT1CODEID
    ,@DESIGNATIONREPORTCODE2ID = DESIGNATIONLEVEL.DESIGNATIONREPORT2CODEID
    ,@STEWARDSHIPPACKAGEPROCESSID = DESIGNATIONLEVEL.STEWARDSHIPPACKAGEPROCESSID
    ,@STEWARDSHIPPACKAGENAME = dbo.UFN_STEWARDSHIPPACKAGE_GETNAME(DESIGNATIONLEVEL.STEWARDSHIPPACKAGEPROCESSID)
    ,@VANITYNAME = DESIGNATIONLEVEL.VANITYNAME
    ,@ENDOWMENTMINAMOUNT = DESIGNATIONLEVEL.ENDOWMENTMINAMOUNT
    ,@ENDOWMENTTARGETDATE = DESIGNATIONLEVEL.ENDOWMENTTARGETDATE
    ,@ENDOWMENTMINAMOUNTDATEMET = DESIGNATIONLEVEL.ENDOWMENTMINAMOUNTDATEMET
    ,@ISFULLYFUNDED = DESIGNATIONLEVEL.ISFULLYFUNDED
    ,@ISENDOWED = DESIGNATIONLEVEL.ISENDOWED
    ,@ADMINISTRATORID = DESIGNATIONLEVEL.ADMINISTRATORID
    ,@ISACCEPTINGFUNDS = DESIGNATIONLEVEL.ISACCEPTINGFUNDS
    ,@ISDISBURSINGMONEY = DESIGNATIONLEVEL.ISDISBURSINGMONEY
    ,@BASECURRENCYID = DESIGNATIONLEVEL.BASECURRENCYID
  from dbo.DESIGNATIONLEVEL
  inner join dbo.DESIGNATIONLEVELTYPE FPT on DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = FPT.ID
  left join dbo.DESIGNATIONLEVELCATEGORYCODE as CATEGORY on DESIGNATIONLEVEL.DESIGNATIONLEVELCATEGORYCODEID = CATEGORY.ID
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(DESIGNATIONLEVEL.ADMINISTRATORID) NF
  left join dbo.DESIGNATIONREPORT1CODE DR1 on DESIGNATIONLEVEL.DESIGNATIONREPORT1CODEID = DR1.ID
  left join dbo.DESIGNATIONREPORT2CODE DR2 on DESIGNATIONLEVEL.DESIGNATIONREPORT2CODEID = DR2.ID
  where DESIGNATIONLEVEL.ID = @ID

  return 0
end