UFN_QUERY_STEWARDSHIPPACKAGEPROCESSEMAILOUTPUT

CREATE function [dbo].[UFN_QUERY_STEWARDSHIPPACKAGEPROCESSEMAILOUTPUT]
(
  @PARAMETERSETID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
returns table
as
return
(
  select
    C.ID as CONSTITUENTID,
    C.NAME as CONSTITUENTNAME,
    D.ID as PURPOSEID, 
    D.NAME as PURPOSENAME,
    SPP.ID as PACKAGEID,
    SPP.NAME as PACKAGENAME,
    C.LOOKUPID as LOOKUPID,
    (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = C.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as ADDRESSEE,
    (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = C.ID and NAMEFORMAT.PRIMARYSALUTATION = 1) as SALUTATION,
    CONTACT.NAME as CONTACT,
    (select top 1 RELATIONSHIP.POSITION
     from dbo.RELATIONSHIP
     where RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
     and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = C.ID) as POSITION,
    EMAILADDRESS.EMAILADDRESS as EMAILADDRESS,
    D.DESCRIPTION as PURPOSEDESCRIPTION,
    D.VANITYNAME as PURPOSEVANITYNAME,
    D.STATEMENTWORDING as PURPOSESTATEMENTWORDING
  from dbo.UFN_DESIGNATIONLEVEL_GETSTEWARDSHIPRECIPIENTS(@PARAMETERSETID, @CURRENTAPPUSERID) S 
  inner join dbo.DESIGNATIONLEVEL D on D.ID = @PARAMETERSETID
  inner join dbo.STEWARDSHIPPACKAGEPROCESS SPP on D.STEWARDSHIPPACKAGEPROCESSID = SPP.ID
  inner join dbo.[UFN_ADDRESSPROCESS_EMAILS](8, @PARAMETERSETID) as EMAILADDRESS on S.CONSTITUENTID = EMAILADDRESS.CONSTITUENTID
  left join dbo.CONSTITUENT C on S.CONSTITUENTID = C.ID
  left join dbo.CONSTITUENT CONTACT on EMAILADDRESS.CONTACTID = CONTACT.ID
)