UFN_QUERY_STEWARDSHIPPACKAGEPROCESSSEASONALOUTPUT

                CREATE function [dbo].[UFN_QUERY_STEWARDSHIPPACKAGEPROCESSSEASONALOUTPUT]
                (
                    @PARAMETERSETID uniqueidentifier = null,
            @CURRENTAPPUSERID uniqueidentifier = null
                )
                returns table
                as
                return 
                (

                select
                    D.ID as ID,                     
                    C.ID as CONSTITUENTID,
                    D.NAME as NAME,
                    D.DESCRIPTION as DESCRIPTION,
                    D.VANITYNAME as VANITYNAME,
                    D.STATEMENTWORDING as STATEMENTWORDING,                    
                    SPP.ID as PACKAGEID,
                    SPP.NAME as PACKAGENAME,
                    C.LOOKUPID as LOOKUPID,
                    C.NAME as CONSTITUENTNAME,                                   
                    (select FORMATTEDNAME from dbo.NAMEFORMAT inner join dbo.NAMEFORMATTYPECODE CODE on NAMEFORMAT.NAMEFORMATTYPECODEID = CODE.ID where CODE.DESCRIPTION = 'Formal Addressee' and NAMEFORMAT.CONSTITUENTID = C.ID) as FORMALADDRESSEE,
                    (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = C.ID and NAMEFORMAT.PRIMARYSALUTATION = 1) as PRIMARYSALUTATION,
                    case when C.ISORGANIZATION = 1 then 
                        (select top(1) 
                            CONTACT.NAME 
                        from
                            dbo.RELATIONSHIP
                        inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
                        where
                            RELATIONSHIP.ISPRIMARYCONTACT = 1
                            and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = C.ID) 
                    else
                        null
                    end as PRIMARYCONTACT,
                    case when C.ISORGANIZATION = 1 then
                        (select top(1)
                            RELATIONSHIP.POSITION
                        from
                            dbo.RELATIONSHIP
                        where
                            RELATIONSHIP.ISPRIMARYCONTACT = 1 and
                            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = C.ID)
                    else
                        null
                    end as PRIMARYPOSITION,
                    ADDRESS.ADDRESSBLOCK as ADDRESSBLOCK,
                    ADDRESS.CITY as CITY,
                    STATE.ABBREVIATION as STATE,
                    ADDRESS.POSTCODE as POSTCODE,
                    COUNTRY.DESCRIPTION as COUNTRY,
                    (select EMAILADDRESS from dbo.EMAILADDRESS where CONSTITUENTID = C.ID and ISPRIMARY = 1) as EMAILADDRESS,
                    (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = C.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as PRIMARYADDRESSEE
                from
                    DBO.UFN_DESIGNATIONLEVEL_GETSTEWARDSHIPRECIPIENTS(@PARAMETERSETID, @CURRENTAPPUSERID) S 
                    inner join dbo.CONSTITUENT C on S.CONSTITUENTID = C.ID
                    inner join DESIGNATIONLEVEL D on D.ID = @PARAMETERSETID
                    left outer join dbo.STEWARDSHIPPACKAGEPROCESS SPP on D.STEWARDSHIPPACKAGEPROCESSID = SPP.ID
                    left join dbo.ADDRESS on
                        C.ID = ADDRESS.CONSTITUENTID
                        and ADDRESS.ISPRIMARY = 1
                        and ADDRESS.DONOTMAIL = 0
                    left join dbo.STATE on ADDRESS.STATEID = STATE.ID
                    left join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
            )